<?php

namespace app\manage\controller;

use app\common\model\ClueErrorLog;
use app\lib\exception\ParamException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Db;
use think\Log;

class Operate extends Base
{

    const SYSTEM_FENGZHU = 1; //凤翥 （非金榕商户的统计） 先付费
    const SYSTEM_JINRONG = 2; //金榕 商户 (只统计金榕商户） 后付费
    public $system_type = [
        self::SYSTEM_FENGZHU => "先付费",
        self::SYSTEM_JINRONG => "后付费",
    ];

    public $system_type_merch_id = [
        self::SYSTEM_FENGZHU => 0,
        self::SYSTEM_JINRONG => 450,
    ];

    public function test(){
        $time='';
        $arr2=Db::name('clue')->field('city,pro_id')->whereTime('create_time', 'between', ['2023-11-15', '2023-12-1'])->where('delete_time',null)->group('city,pro_id')->select();
        $arr1=Db::name('clue_cost_log')->field('city,pro_id')->whereTime('create_time', 'between', ['2023-11-15', '2023-12-1'])->group('city,pro_id')->select();
//        dump($clueLog);
//        $arr3 = array_filter($arr1, function($v) use ($arr2) { return ! in_array($v, $arr2);});
        $arr3 = array_udiff($arr1, $arr2, 'compare_arrays');
        dump($arr3);
    }

    //人群包
    public function userMd5(){
        // 查询条件start
        $clue_id = input('clue_id','');
        $clue_name = input('clue_name','');
        $phone = input('phone','');
        $status = input('status/d',99);
        $times = input('times','');
        $pro_id = input('pro_id','');
        $city = input('city','');

        $cityname = input('cityname','');
        $ac_name = input('ac_name','');
        $adv_id = input('adv_id','');
        $mer_name = input('mer_name','');

        $map = [];
        if($clue_id){
            $map['a.clue_id']=$clue_id;
        }
        if($clue_name != ''){
            $map['a.clue_name'] = ['like','%'.$clue_name.'%'];
        }
        if($phone != 0){
            $map['a.phone'] = $phone;
        }
//        if($source != '0'){
//            $map['source'] = $source;
//        }
        if($times != ''){
            $time_arr = explode(' - ',$times);
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
        }
        if($status!=99){
            $map['a.status']=$status;
        }
        if($pro_id!=''){
            $map['a.pro_id']=$pro_id;
        }
        if(!empty($city)){
            $map['a.city']=$city;
        }
        if(!empty($err_msg)){
            $map['a.err_msg']=['like','%'.$err_msg.'%'];
        }

        if($adv_id){
            $map['a.adv_id']=$adv_id;
        }
        if(!empty($mer_name)){
            $mArr=model('Merchant')->where('mer_name','like','%'.$mer_name.'%')->column('id');
            if($mArr){
                $map['b.mer_id']=['in',$mArr];
            }else{
                $map['a.id']=0;
            }
        }
        //模糊查询城市
        if(!empty($cityname)){
            $cityIdArr=Db::name('city')->field('id,type,pid')->where('name','like',$cityname."%")->where('type',2)->find();
            if($cityIdArr){
                $map['a.city']=$cityIdArr['pid'].'-'.$cityIdArr['id'];
            }else{
                $map['a.city']=0;
            }
        }
        if(!empty($ac_name)){
            $adv_idArr=model('Account')->where('acc_name','like','%'.$ac_name.'%')->column('adv_id');
            if(!empty($adv_idArr)){
                $map['a.adv_id']=['in',$adv_idArr];
            }else{
                $map['a.adv_id']=0;
            }
        }
        // 查询条件end

        $channels = $this->getChannelArray();
//        $products = model('Product')->getListByMap();
        $productAll =Db::name('product')->column('name','id');
        $products = model('Product')->where('status',1)->column('name','id');

        $total = model('Clue')->alias('a')
            ->where($map)
            ->group('phone')
            ->count();

//        $infos = model('Clue')->alias('a')->field('a.*')
//            ->order('a.id desc')
//            ->where($map)
//            ->group('phone')
//            ->paginate();
//
//        foreach ($infos as $k=>$v){
//
//            $infos[$k]['md5p']=md5($v['phone']);
//            $infos[$k]['product_name']=empty($v['pro_id'])?'未知':$productAll[$v['pro_id']];
//
//            if($v['city'] != ''){
//                $ids = explode('-',$v['city']);
//                $cityNum=count($ids);
//                if($cityNum==1){
//                    $infos[$k]['city'] = $this->getCityName($ids[0]);
//                }elseif ($cityNum==2){
//                    $prov = $this->getCityName($ids[0]);
//                    $city = $this->getCityName($ids[1]);
//                    if($prov == $city){
//                        $infos[$k]['city'] = $prov;
//                    }else{
//                        $infos[$k]['city'] = $prov.'-'.$city;
//                    }
//                }
//            }
//            if($v['push_time']){
//                $infos[$k]['push_time'] = date('Y-m-d H:i:s',$v['push_time']);
//            }
//
//            $infos[$k]['acc_name'] = '手动添加的线索';
//            $infos[$k]['acc_desc'] = '手动添加';
//            $infos[$k]['channel_name'] = '后台系统';
//            if($v['adv_id'] != ''){
//                $account = Db::name('account')->where('adv_id','=',$v['adv_id'])->find();
//                if($account){
//                    $infos[$k]['acc_name'] = $account['acc_name'];
//                    $infos[$k]['acc_desc'] = $account['acc_desc'];
//                    $infos[$k]['channel_name'] = $channels[$account['channel_id']];
//                }else{
//                    $infos[$k]['acc_name'] = '未知账号';
//                    $infos[$k]['acc_desc'] = '';
//                    $infos[$k]['channel_name'] = $channels[$v['type']];
//                }
//            }
//        }

        $province = Db::name('city')->where(['pid'=>1])->select();

        return $this->fetch('',[
            'infos' => [],
            'clue_name' => $clue_name,
            'phone' => $phone,
            'times' => $times,
            'sources' => [],
            'province' => $province,
            'status' => $status,
            'pro_id' => $pro_id,
            'products' => $products,
            'err_msg' => '',
            'cityname' => $cityname,
            'ac_name' => $ac_name,
            'adv_id' => $adv_id,
            'mer_name' => $mer_name,
            'total' => $total,
        ]);
    }
    //md5导出
    public function userMd5Export(){
        // 查询条件start
        $clue_id = input('clue_id','');
        $clue_name = input('clue_name','');
        $phone = input('phone','');
        $status = input('status/d',99);
        $times = input('times','');
        $pro_id = input('pro_id','');
        $city = input('city','');

        $cityname = input('cityname','');
        $ac_name = input('ac_name','');
        $adv_id = input('adv_id','');
        $mer_name = input('mer_name','');

        $map = [];
        if($clue_id){
            $map['a.clue_id']=$clue_id;
        }
        if($clue_name != ''){
            $map['a.clue_name'] = ['like','%'.$clue_name.'%'];
        }
        if($phone != 0){
            $map['a.phone'] = $phone;
        }
//        if($source != '0'){
//            $map['source'] = $source;
//        }
        $timeName='';
        if($times != ''){
            $time_arr = explode(' - ',$times);
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
            $timeName=date('YmdHi',strtotime($time_arr[0])).'-'.date('YmdHi',strtotime($time_arr[1]));
        }
        if($status!=99){
            $map['a.status']=$status;
        }
        if($pro_id!=''){
            $map['a.pro_id']=$pro_id;
        }
        if(!empty($city)){
            $map['a.city']=$city;
        }
        if(!empty($err_msg)){
            $map['a.err_msg']=['like','%'.$err_msg.'%'];
        }

        if($adv_id){
            $map['a.adv_id']=$adv_id;
        }
        if(!empty($mer_name)){
            $mArr=model('Merchant')->where('mer_name','like','%'.$mer_name.'%')->column('id');
            if($mArr){
                $map['b.mer_id']=['in',$mArr];
            }else{
                $map['a.id']=0;
            }
        }
        //模糊查询城市
        if(!empty($cityname)){
            $cityIdArr=Db::name('city')->field('id,type,pid')->where('name','like',$cityname."%")->where('type',2)->find();
            if($cityIdArr){
                $map['a.city']=$cityIdArr['pid'].'-'.$cityIdArr['id'];
            }else{
                $map['a.city']=0;
            }
        }
        if(!empty($ac_name)){
            $adv_idArr=model('Account')->where('acc_name','like','%'.$ac_name.'%')->column('adv_id');
            if(!empty($adv_idArr)){
                $map['a.adv_id']=['in',$adv_idArr];
            }else{
                $map['a.adv_id']=0;
            }
        }


        $filename="./usermd5/人群".date('Y-m-d').'&'.$timeName.time().'.txt';
        if(file_exists($filename))
        {
            $file_url = $filename;  // 文件路径
            header("Content-Type: application/octet-stream");
            header("Content-Disposition: attachment; filename=" . basename($file_url));
            header("Content-Length: " . filesize($file_url));
            readfile($file_url);
            exit;
        }
        $handle=fopen($filename,"a+");
        $str=fwrite($handle,"");


        $infos = model('Clue')->alias('a')->field('a.id,a.phone')
//            ->order('a.id desc')
            ->where($map)
            ->group('phone')
            ->chunk(500, function($lists) use ($handle)  {
//                dump($lists);
                foreach ($lists as $user){

                    $md5=md5($user['phone']);
                    $str=fwrite($handle,$md5."\n");
                }
            });

        fclose($handle);

        $file_url = $filename;  // 文件路径
        header("Content-Type: application/octet-stream");
        header("Content-Disposition: attachment; filename=" . basename($file_url));
        header("Content-Length: " . filesize($file_url));
        readfile($file_url);
        exit;
    }
    //城市复购率
    public function cityReRate()
    {
        $pro_name = input('pro_name','');
        $province = input('province',0);
        $orderb = input('orderb','');
        $status = input('status/d',0);
        $channel_id = input('channel_id/a',0);
        $pro_id = input('pro_id/d',0);
        $swadmin = input('swadmin/d',0);
        $type = input('type/d',1);//1城市复购率2商户复购率
        $mer_name = input('mer_name','');//1城市复购率2商户复购率

        $map = [];
//        if($pro_name != ''){
//            $products = Db::name('product')->field('id')->where(['name'=>['like','%'.$pro_name.'%']])->select();
//            $pro_id_list = array_column($products, 'id');
//            $map['pro_id'] = ['in',$pro_id_list];
//        }

        $map1=[];
        $map2=[];
        $map3=[];
        $mapa=[];
        if(!empty($province)){
            $map['a.city']=['like',$province.'-'.'%'];
        }
        if(!empty($channel_id)){
            if(count($channel_id)==1&&$channel_id[0]==0){

            }else{
                $map['a.channel_id']=['in',$channel_id];

                $channelStr=[];
                foreach ($channel_id as $v){
                    $channelStr[]='find_in_set("'.$v.'",channel_id)';
                }
                $mapa=implode(' or ',$channelStr);

                $map1['type']=['in',$channel_id];
                $map2['channel_id']=['in',$channel_id];
            }
        }
        if(!empty($pro_id)){
            $map['a.pro_id']=$pro_id;
            $map1['pro_id']=$pro_id;
            $map2['pro_id']=$pro_id;
        }

        if(!empty($swadmin)){
            $map3['bind_aid|weihu_aid']=$swadmin;
        }

        if($mer_name!=''){
            $merchant=model('Merchant')->where('mer_name','like','%'.$mer_name.'%')->column('id');
            if(!empty($merchant)){
                $map['a.mer_id']=['in',$merchant];
            }else{
                $map['a.mer_id']=0;
            }
        }

        $infoTemp = model('AuthGroupAccess')->alias('a')
            ->field('a.*,b.phone,b.realname')
            ->join('admin b','a.uid=b.id')
            ->where(function ($q){
                $q->where('a.group_id',4)->whereOr('a.group_id',5)->whereOr('a.group_id',1);
            })
//        ->where('a.group_id',4)
            ->where('b.delete_time',NULL)->select();



//        if(in_array($this->group_id,[4,5])){
//            $map3['bind_aid|weihu_aid']=$this->uid;
//            $map3['status']=1;
//            $mArr=model('merchant')->where($map3)->column('id');
//            if($mArr){
//                $map['a.mer_id']=['in',$mArr];
//            }else{
//                $map['a.mer_id']=0;
//            }
//        }

        $orderDy='a.id desc';
        if(!empty($orderb)){
            if($orderb==1){
                $orderDy='balance asc';
            }elseif($orderb==2){
                $orderDy='balance desc';
            }elseif($orderb==3){
                $orderDy='price asc';
            }elseif($orderb==4){
                $orderDy='price desc';
            }
        }

        if($type==1){
            $groupBy='a.city';
            $typeDesc='城市';
            $typeDesc2='商家数';
        }else{
            $groupBy='mer_id';
            $typeDesc='商户';
            $typeDesc2='规则数';
        }

        if($status==1){
            $map['a.status']=1;
            $map['b.status']=1;
            $map['b.start_time']=['<=',date('Y-m-d H:i:s')];
            $map['b.end_time']=['>=',date('Y-m-d H:i:s')];

            $infos = model('push')->alias('a')
                ->join('merchant b','b.id=a.mer_id')
                ->field('count(a.id) num,a.city,sum(a.up_limit) up_limit,sum(balance) balance,avg(price) price')
                ->where($map)
                ->where($map3)
                ->where($mapa)
                ->order($orderDy)->group($groupBy)->paginate();
        }elseif($status==2){
            $infos = model('push')->alias('a')
                ->join('merchant b','b.id=a.mer_id')
                ->field('count(a.id) num,a.city,sum(a.up_limit) up_limit,sum(balance) balance,avg(price) price')
                ->where($map)
                ->where($map3)
                ->where($mapa)
                ->where("a.status=2 or b.status=2 or start_time >='".date('Y-m-d H:i:s')."' or end_time <='".date('Y-m-d H:i:s')."'")
                ->order($orderDy)->group($groupBy)->paginate();
        }else{
            $infos = model('push')->alias('a')
                ->join('merchant b','b.id=a.mer_id')
                ->field('mer_id,count(a.id) num,a.city,avg(price) price,a.id,b.mer_name,b.mer_desc,b.username,b.phone')
                ->where($map)
                ->where($map3)
                ->where($mapa)
                ->order($orderDy)->group($groupBy)->paginate();
        }
//        dump($infos);die;

        $totalMer=model('push')->alias('a')
            ->join('merchant b','b.id=a.mer_id')
            ->where($map)->where($map3)->where($mapa)
            ->group('b.id')
//            ->where(['a.status'=>1,'b.status'=>1])
            ->column('b.id');
        $nowShopNum=count($totalMer);

        $nowReNum=model('charge')->whereIn('mer_id',$totalMer)->field('mer_id,count(id) num')->where('fz_money','>=',500)->group('mer_id')->having('num >= 2')->count();

        $nowReRate=0;


//        dump($totalMer);
//        dump($nowReNum);


        foreach ($infos as $k=>$v){

            $infos[$k]['price']=round($v['price']);
            $infos[$k]['num']=$v['num'];
            //符合规则的商家
            if($type==1){
                $merchant=model('push')->alias('a')->field('mer_id')
                    ->join('merchant b','b.id=a.mer_id')
                    ->where($map)->where($map3)->where($mapa)->where(['a.city'=>$v['city']])->select();
                //复购商家
                $infos[$k]['re_num']=0;

                //查询商家充值
                foreach ($merchant as $vv){
                    $logList=model('charge')->where('mer_id',$vv['mer_id'])->where('fz_money','>=',500)->limit(2)->select();
                    if(count($logList)==2){
                        $infos[$k]['re_num']+=1;
                    }
                }
                $ids = explode('-',$v['city']);
                $cityNum=count($ids);
                if($cityNum==1){
                    $infos[$k]['city'] = $this->getCityName($ids[0]);
                }elseif ($cityNum==2){
                    $prov = $this->getCityName($ids[0]);
                    $city = $this->getCityName($ids[1]);
                    if($prov == $city){
                        $infos[$k]['city'] = $prov;
                    }else{
                        $infos[$k]['city'] = $prov.'-'.$city;
                    }
                }
            }else{
                $logList=model('charge')->where('mer_id',$v['mer_id'])->where('fz_money','>=',500)->limit(2)->select();
                if(count($logList)==2){
                    $infos[$k]['re_num']=1;
                }else{
                    $infos[$k]['re_num']=0;
                }

                $infos[$k]['city']=$v['mer_name'];

            }


            $infos[$k]['re_num_rate']=bcdiv($infos[$k]['re_num'],$infos[$k]['num'],4)*100;


//            $nowShopNum+=$v['num'];
//            $nowReNum+=$infos[$k]['re_num'];

        }
//        die;
        if($nowShopNum>0){
            $nowReRate=bcdiv($nowReNum,$nowShopNum,4)*100;
        }

        $provinces = Db::name('city')->where(['pid'=>1])->select();
        $products = model('Product')->getListByMap();
//        $merchants = model('Merchant')->getListByMap([],'','',false);
        $channels = $this->getChannelArray();

        if(is_array($channel_id)){
            $channel_id=implode(',',$channel_id);
        }else{
            $channel_id=0;
        }

        return $this->fetch('',[
            'infos' => $infos,
//            'pro_name' => $pro_name,
            'province' => $provinces,
            'sprovince' => $province,
            'orderb' => $orderb,
            'status' => $status,
            'channel_id' => $channel_id,
            'pro_id' => $pro_id,
            'products' => $products,
            'channels' => $channels,
            'group_id' => $this->group_id,
            'nowShopNum' => $nowShopNum,
            'nowReNum' => $nowReNum,
            'nowReRate' => $nowReRate,
            'infoTemp' => $infoTemp,
            'swadmin' => $swadmin,
            'typeDesc' => $typeDesc,
            'type' => $type,
            'mer_name' => $mer_name,
            'typeDesc2' => $typeDesc2,
        ]);
    }
    //城市复购率
    public function cityReRateExprot()
    {
        $pro_name = input('pro_name','');
        $province = input('province','');
        $orderb = input('orderb','');
        $status = input('status/d',0);
        $channel_id = input('channel_id',0);
        $pro_id = input('pro_id/d',0);
        $swadmin = input('swadmin/d',0);
        $type = input('type/d',1);//1城市复购率2商户复购率
        $mer_name = input('mer_name','');//1城市复购率2商户复购率

        $map = [];
//        if($pro_name != ''){
//            $products = Db::name('product')->field('id')->where(['name'=>['like','%'.$pro_name.'%']])->select();
//            $pro_id_list = array_column($products, 'id');
//            $map['pro_id'] = ['in',$pro_id_list];
//        }

        $map1=[];
        $map2=[];
        $map3=[];
        $mapa=[];
        if(!empty($province)){
            $map['a.city']=['like',$province.'-'.'%'];
        }
        if(!empty($channel_id)){
            $channel_id=explode(',',$channel_id);
            if(count($channel_id)==1&&$channel_id[0]==0){

            }else{
//                $map['a.channel_id']=['in',$channel_id];

                $channelStr=[];
                foreach ($channel_id as $v){
                    $channelStr[]='find_in_set("'.$v.'",channel_id)';
                }
                $mapa=implode(' or ',$channelStr);

                $map1['type']=['in',$channel_id];
                $map2['channel_id']=['in',$channel_id];
            }
        }
        if(!empty($pro_id)){
            $map['a.pro_id']=$pro_id;
            $map1['pro_id']=$pro_id;
            $map2['pro_id']=$pro_id;
        }

        if(!empty($swadmin)){
            $map3['bind_aid|weihu_aid']=$swadmin;
        }

        if($mer_name!=''){
            $merchant=model('Merchant')->where('mer_name','like','%'.$mer_name.'%')->column('id');
            if(!empty($merchant)){
                $map['a.mer_id']=['in',$merchant];
            }else{
                $map['a.mer_id']=0;
            }
        }

//        if(in_array($this->group_id,[4,5])){
//            $map3['bind_aid|weihu_aid']=$this->uid;
//            $map3['status']=1;
//            $mArr=model('merchant')->where($map3)->column('id');
//            if($mArr){
//                $map['a.mer_id']=['in',$mArr];
//            }else{
//                $map['a.mer_id']=0;
//            }
//        }

        $orderDy='a.id desc';
        if(!empty($orderb)){
            if($orderb==1){
                $orderDy='balance asc';
            }elseif($orderb==2){
                $orderDy='balance desc';
            }elseif($orderb==3){
                $orderDy='price asc';
            }elseif($orderb==4){
                $orderDy='price desc';
            }
        }

        if($type==1){
            $groupBy='a.city';
            $typeDesc='城市';
            $typeDesc2='商家数';
        }else{
            $groupBy='mer_id';
            $typeDesc='商户';
            $typeDesc2='规则数';
        }

        if($status==1){
            $map['a.status']=1;
            $map['b.status']=1;
            $map['b.start_time']=['<=',date('Y-m-d H:i:s')];
            $map['b.end_time']=['>=',date('Y-m-d H:i:s')];

            $infos = model('push')->alias('a')
                ->join('merchant b','b.id=a.mer_id')
                ->field('count(a.id) num,a.city,sum(a.up_limit) up_limit,sum(balance) balance,avg(price) price')
                ->where($map)
                ->where($map3)
                ->where($mapa)
                ->order($orderDy)->group($groupBy)->select();
        }elseif($status==2){
            $infos = model('push')->alias('a')
                ->join('merchant b','b.id=a.mer_id')
                ->field('count(a.id) num,a.city,sum(a.up_limit) up_limit,sum(balance) balance,avg(price) price')
                ->where($map)
                ->where($map3)
                ->where($mapa)
                ->where("a.status=2 or b.status=2 or start_time >='".date('Y-m-d H:i:s')."' or end_time <='".date('Y-m-d H:i:s')."'")
                ->order($orderDy)->group($groupBy)->select();
        }else{
            $infos = model('push')->alias('a')
                ->join('merchant b','b.id=a.mer_id')
                ->field('mer_id,count(a.id) num,a.city,avg(price) price,a.id,b.mer_name,b.mer_desc,b.username,b.phone')
                ->where($map)
                ->where($map3)
                ->where($mapa)
                ->order($orderDy)->group($groupBy)->select();
        }

        if(empty($infos)){
            echo '没有数据导出';
        }

        foreach ($infos as $k=>$v){

            $infos[$k]['price']=round($v['price']);
            $infos[$k]['num']=$v['num'];
            //符合规则的商家
            if($type==1){
                $merchant=model('push')->alias('a')->field('mer_id')
                    ->join('merchant b','b.id=a.mer_id')
                    ->where($map)->where($map3)->where($mapa)->where(['a.city'=>$v['city']])->select();
                //复购商家
                $infos[$k]['re_num']=0;

                //查询商家充值
                foreach ($merchant as $vv){
                    $logList=model('charge')->where('mer_id',$vv['mer_id'])->where('fz_money','>=',500)->limit(2)->select();
                    if(count($logList)==2){
                        $infos[$k]['re_num']+=1;
                    }
                }
                $ids = explode('-',$v['city']);
                $cityNum=count($ids);
                if($cityNum==1){
                    $infos[$k]['city'] = $this->getCityName($ids[0]);
                }elseif ($cityNum==2){
                    $prov = $this->getCityName($ids[0]);
                    $city = $this->getCityName($ids[1]);
                    if($prov == $city){
                        $infos[$k]['city'] = $prov;
                    }else{
                        $infos[$k]['city'] = $prov.'-'.$city;
                    }
                }
            }else{
                $logList=model('charge')->where('mer_id',$v['mer_id'])->where('fz_money','>=',500)->limit(2)->select();
                if(count($logList)==2){
                    $infos[$k]['re_num']=1;
                }else{
                    $infos[$k]['re_num']=0;
                }

                $infos[$k]['city']=$v['mer_name'];

            }


            $infos[$k]['re_num_rate']=bcdiv($infos[$k]['re_num'],$infos[$k]['num'],4)*100;

        }

        //导出
        $newExcel = new Spreadsheet();
//获取当前操作sheet的对象
        $objSheet = $newExcel->getActiveSheet();
        $date = date('Ymd',time());
        $name = '城市复购率';
        $objSheet->setTitle($name);//设置当前sheet的标题
//设置表头

        $objSheet->setCellValue('A1', $typeDesc)
            ->setCellValue('B1', $typeDesc2)
            ->setCellValue('C1', '复购数')
            ->setCellValue('D1', '复购率')
            ->setCellValue('E1', '平均分发价');

        $objSheet->getColumnDimension('A')->setWidth(20);
        $objSheet->getColumnDimension('B')->setWidth(10);
        $objSheet->getColumnDimension('C')->setWidth(10);
        $objSheet->getColumnDimension('D')->setWidth(10);
        $objSheet->getColumnDimension('D')->setWidth(20);

        $info = $infos;
        $dataCount = count($info);
        $k = 1;
        if($dataCount == 0){
            exit;
        }else {
            for ($i = 0; $i < $dataCount; $i++) {
                $k = $k + 1;
                $objSheet->setCellValue('A' . $k, $info[$i]['city'])
                    ->setCellValue('B' . $k, $info[$i]['num'])
                    ->setCellValue('C' . $k, $info[$i]['re_num'])
                    ->setCellValue('D' . $k, $info[$i]['re_num_rate'].'%')
                    ->setCellValue('E' . $k, $info[$i]['price']);
            }
        }


        $filename = date('Y-m-d').$name.'导出';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename='.$filename.'.xlsx');
        header('Cache-Control: max-age=0');
//
        $write = IOFactory::createWriter($newExcel, 'Xlsx');
        $write->save('php://output');

    }

    //城市备注
    public function cityRemark(){
        $cityname = input('cityname','');

        $map = [];

        //模糊查询城市
        if(!empty($cityname)){
            $map['a.name']=['like',$cityname."%"];
        }

        $map['a.type']=2;
//        $infos = model('Push')->getInfosByMap($map, '', false);
        $infos =model('city')->alias('a')
            ->where($map)->order('id asc')->paginate();

        $productAll =Db::name('product')->column('name','id');
        foreach ($infos as &$v){
            $v['province']=Db::name('city')->where('id',$v['pid'])->value('name');
            if(!empty($v['update_time'])){
                $v['time']=date('Y-m-d H:i:s',$v['update_time']);
            }else{
                $v['time']='';
            }

            $proPrice=json_decode( $v['pro_price'],true);
            $endPrice=[];
            if($proPrice){
                foreach ($proPrice as $kk=>$vv){
                    $proName=$productAll[$kk];
                    $endPrice[]=['id'=>$kk,'name'=>$proName,'pro_price'=>$vv];
                }
            }
            $v['pro_price']=$endPrice;
        }

        $products = model('Product')->where('status',1)->column('name','id');

        return $this->fetch('',[
            'infos' => $infos,
            'cityname' => $cityname,
            'products' => $products,
        ]);
    }

    public function citySave(){
        if(request()->isAjax()){
            try{
                $id = input('post.id');
                $remark = input('post.remark');
                $pro_price = input('post.pro_price/a');

                $pro_priceArr=[];
                foreach ($pro_price as $k=>$v){
                    if($v>0){
                        $pro_priceArr[$k]=$v;
                    }
                }

                $isOK=Db::name('city')->where('id',$id)->update(['remark'=>$remark,'update_time'=>time(),'pro_price'=>json_encode($pro_priceArr,JSON_UNESCAPED_UNICODE)]);

                if($isOK){
                    return self::showReturnCode(1001,'保存成功');
                }else{
                    return self::showReturnCode(1011,'保存失败');
                }

            }catch (\Exception $e){
                Log::error($e->getMessage());
                return self::showReturnCode(1011,$e->getMessage());
            }
        }else{
            return self::showReturnCode(1002);
        }
    }

    public function cityEdit()
    {
        $id = input('id');
        if(!$id) return self::showReturnCode(1002,'id 错误');
        $info = model('City')->getInfoByMap(['id'=>$id]);
        $info['pro_price']=json_decode($info['pro_price']);
        return self::showJsonReturnCodeData('1001',$info,'');
    }

    //账户投放统计
    public function accountClueTotalAccount(){
        $times = input('times','');
        $acc_name = input('acc_name','');
        $adv_id = input('adv_id','');
        $pro_id = input('pro_id','');
        $channel_id = input('channel_id','');
        $groupType = input('gtype',1);//1账户维度2城市维度3商户维度
        $cityname = input('cityname','');
        $youhua_id = input('youhua_id','');
        $isKong = input('is_kong','');

        $map = [];
        $map1 = [];
        $map3 = [];
        $map4 = [];
        $map5 = [];


        if($groupType==1){
            if($adv_id){
                $map['a.adv_id']=$adv_id;
                $map4['b.adv_id']=$adv_id;
            }
            if($acc_name != ''){
                $mapa['acc_name'] = ['like','%'.$acc_name.'%'];
                $account=model('account')->where($mapa)->value('adv_id');
                if($account){
                    $map['a.adv_id']=$account;
                    $map4['b.adv_id']=$account;
                }else{
                    $map['a.adv_id']=0;
                    $map4['b.adv_id']=0;
                }
            }

            if($channel_id != 0){
                $map['a.type'] = $channel_id;
                $map4['b.channel_id'] = $channel_id;
            }
        }else{
            //模糊查询城市
            if(!empty($cityname)){
                $cityIdArr=Db::name('city')->field('id,type,pid')->where('name','like',$cityname."%")->where('type',2)->find();
                if($cityIdArr){
                    $map['a.city']=$cityIdArr['pid'].'-'.$cityIdArr['id'];
                }else{
                    $map['a.city']=0;
                }
            }
        }

        if($isKong=='1'){
            $map5['money']=['>',0];
            $map5['transform_val']=['=',0];
        }

        if($youhua_id!=''){
            $map['bind_youhua']=$youhua_id;
            $map4['bind_youhua']=$youhua_id;
        }

        if($this->group_id==7){
            $map['bind_youhua']=$this->uid;
            $map4['bind_youhua']=$this->uid;
        }


        if($pro_id!=''){
            $map['a.pro_id']=$pro_id;
            $map4['a.pro_id']=$pro_id;
        }

        if($times != ''){
            $time_arr = explode(' - ',$times);
            $showTime=$times;
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
            $map2['time'] = ['between',[$time_arr[0],$time_arr[1]]];
        }else{
            $times=date('Y-m-d').' 00:00:00 - '.date('Y-m-d').' 23:59:59';
            $showTime=date('Y-m-d').' - '.date('Y-m-d');
            $time_arr = explode(' - ',$times);
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
            $map2['time'] = ['between',[$time_arr[0],$time_arr[1]]];
        }

        $orderDy='a.id desc';

        $channels=$this->getChannelArray();
        $productArr = Db::name('product')->column('name','id');
        $products = model('Product')->where('status',1)->column('name','id');

        if($groupType==1){

            $accountArr=Db::name('clue_cost_log')->alias('a')->field('b.*,a.account_id,a.pro_id')
                ->join('account b','b.id=a.account_id','Left')
                ->where($map4)->where($map2)->group('account_id,pro_id')->select();

            $group='a.adv_id,a.pro_id';
            $infos=model('Clue')->alias('a')->field('c.clue_rate,count(a.id) clue_num,sum(b.price) price,a.adv_id,a.pro_id,a.type,c.acc_name,c.acc_desc,c.show_channel,c.id account_id,SUM(IF(a.status = 1,1,0)) push_num,bind_youhua')
                ->join('clue_log b','b.clue_id=a.id','LEFT')
                ->join('account c','c.adv_id=a.adv_id','LEFT')
                ->where($map)->group($group)->order($orderDy)->select();



            foreach ($accountArr as $v){
                $isPro=true;
                $proType=$v['pro_id'];
                foreach ($infos as $vv){
                    if($v['account_id']==$vv['account_id']&&$v['pro_id']==$vv['pro_id']){
                        $isPro=false;
                    }
                }
                if($isPro){//没有这个商户
                    $infos[]=[
                        'account_id'=>$v['id'],
                        'clue_rate'=>$v['clue_rate'],
                        'clue_num'=>0,
                        'price'=>0,
                        'adv_id'=>$v['adv_id'],
                        'pro_id'=>$proType,
                        'type'=>$v['channel_id'],
                        'acc_name'=>$v['acc_name'],
                        'acc_desc'=>$v['acc_desc'],
                        'show_channel'=>$v['show_channel'],
                        'push_num'=>0,
                        'bind_youhua'=>$v['bind_youhua'],
                    ];
                }
            }
//        }elseif($groupType==2){
//            $group='a.city,a.pro_id';
//            $infos=model('Clue')->alias('a')->field('a.city,c.clue_rate,count(a.id) clue_num,sum(b.price) price,a.adv_id,a.pro_id,a.type,c.acc_name,c.acc_desc,show_channel,c.id account_id,SUM(IF(a.status = 1,1,0)) push_num')
//                ->join('clue_log b','b.clue_id=a.id','LEFT')
//                ->join('account c','c.adv_id=a.adv_id','LEFT')
//                ->where($map)->group($group)->order($orderDy)->paginate();
//        }elseif ($groupType==3){
//            $group='b.mer_id,a.pro_id';
//            $infos=model('Clue')->alias('a')->field('mer_name,mer_desc,username,d.phone mer_phone,a.city,c.clue_rate,count(a.id) clue_num,sum(b.price) price,a.adv_id,a.pro_id,a.type,c.acc_name,c.acc_desc,show_channel,c.id account_id,SUM(IF(a.status = 1,1,0)) push_num')
//                ->join('clue_log b','b.clue_id=a.id','LEFT')
//                ->join('account c','c.adv_id=a.adv_id','LEFT')
//                ->join('merchant d','d.id=b.mer_id','LEFT')
//                ->where($map)->group($group)->order($orderDy)->paginate();
        }else{
            $infos=[];
        }

        $row=count($infos);

        $nowXiaoHao=0; //导入的消耗
        $nowZhuanghua=0; //导入的转换量
        $nowChengBen=0; //成本=消耗/转化
        $apiJinXianXiaohao=0;//导入计算的真实消耗
        $apiJixXianNum=0;
        $jinXianShunHaoRate=0;//1-（API进线量/转化量）
        $apiJinXianChengBen=0;//消耗/API进线量
        $nowFenfaNum=0;
        $fenFaShunHaoRare=0;//1-(分发数量/进线量);
        $allShunHaoRare=0;//1-(推送/转化);
        $reClueNum=0;
        $rePhone=0;
        $reCity=0;
        $pushMoney=0;
        $pushAvgMoney=0;//分发金额/分发数量
        $nowShouFaAllCha=0;
        $nowKeDanCha=0;//分发均价-真实成本;

        foreach ($infos as &$v){
            $v['shop_name']='未知';
            $v['shop_desc']='未知';
            $v['shop_username']='未知';
            $v['shop_phone']='未知';

            $v['pro_name']=$productArr[$v['pro_id']];
            //账户消耗
            $v['city_name']='未知';
            $v['channel_name']='';
            $v['acc']='';
            if($groupType==1){
                $v['channel_name']=$channels[$v['type']];
                $map3=['account_id'=>$v['account_id'],'pro_id'=>$v['pro_id']];
//            }elseif($groupType==2){
//                $map3=['city'=>$v['city'],'pro_id'=>$v['pro_id']];
//                if($v['city'] != ''){
//                    $ids = explode('-',$v['city']);
//                    $cityNum=count($ids);
//                    if($cityNum==1){
//                        $v['city_name'] = $this->getCityName($ids[0]);
//                    }elseif ($cityNum==2){
//                        $prov = $this->getCityName($ids[0]);
//                        $city = $this->getCityName($ids[1]);
//                        if($prov == $city){
//                            $v['city_name'] = $prov;
//                        }else{
//                            $v['city_name'] = $prov.'-'.$city;
//                        }
//                    }
//                }
//            }elseif($groupType==3){
//                $v['shop_name']=$v['mer_name'];
//                $v['shop_desc']=$v['mer_desc'];
//                $v['shop_username']=$v['username'];
//                $v['shop_phone']=$v['mer_phone'];

                if($v['bind_youhua']){
                    $v['youhua_name']=Db::name('admin')->where('id',$v['bind_youhua'])->value('realname');
                }else{
                    $v['youhua_name']='';
                }
            }
            $xiaohaoMoney=Db::name('clue_cost_log')->field('sum(true_consume) true_consume,sum(money) money,sum(transform_val) transform_val,sum(clue_num) clue_num')->where($map3)->where($map2)->where($map5)->find();
            $v['e_money']='-';//账户消耗
            $v['e_zhuanhua']='-';//账户转化量
            $v['e_clue_num']='-';//导入进线量
            $v['e_chengben']='-';//成本计算
            $v['e_true_consume']='-';//Api进线消耗（真实消耗）
            $v['jinxiansunhao_rate']='-';//进线损耗率
            $v['avg_api_clue']='-';//真实成本

            if($v['clue_num']>0){
                //分发损耗率
                $v['push_rate']=bcsub(1,bcdiv($v['push_num'],$v['clue_num'],4),4)*100;
                $v['all_push_rate']='-';//总损耗率
                //重复
                $v['re_clue_num']=Db::name('clue_err_log')->alias('a')->where(['type'=>1,'adv_id'=>$v['adv_id'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_clue_rate']=bcdiv($v['re_clue_num'],$v['clue_num'],4)*100;
                //撞库
                $v['re_phone']=Db::name('clue_err_log')->alias('a')
//                    ->join('clue b','a.clue_id=b.id AND b.status=0')
                    ->where(['a.type'=>2,'a.adv_id'=>$v['adv_id'],'a.pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_phone_rate']=bcdiv($v['re_phone'],$v['clue_num'],4)*100;
                //非目标城市
                $v['re_clue_city']=Db::name('clue_err_log')->alias('a')
//                    ->join('clue b','a.clue_id=b.id AND b.status=0')
                    ->where(['a.type'=>3,'a.adv_id'=>$v['adv_id'],'a.pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_city_rate']=bcdiv($v['re_clue_city'],$v['clue_num'],4)*100;
            }else{
                $v['push_rate']='-';
                $v['all_push_rate']='-';//总损耗率
                //重复
                $v['re_clue_num']=Db::name('clue_err_log')->alias('a')->where(['type'=>1,'adv_id'=>$v['adv_id'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_clue_rate']='-';
                //撞库
                $v['re_phone']=Db::name('clue_err_log')->alias('a')->where(['type'=>2,'adv_id'=>$v['adv_id'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_phone_rate']='-';
                //非目标城市
                $v['re_clue_city']=Db::name('clue_err_log')->alias('a')->where(['type'=>3,'adv_id'=>$v['adv_id'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_city_rate']='-';
            }

            //分发客单
            if($v['push_num']>0){
                $v['push_avg_money']=bcdiv($v['price'],$v['push_num'],2);
            }else{
                $v['push_avg_money']=0;
            }

            $v['e_push_avg_money']='-';

            //收发总差额(分发总金额-(账户消耗*(1-返点)))，
            $v['e_sf_all_cha']='-';
//            $temp1=bcmul($v['price'],bcsub(1,$v['clue_rate']/100,3),2);
//            $v['sf_all_cha']=bcsub($v['price'],$temp1,2);
            //收发客单差额
            $v['e_sf_avg_sub']='-';

            if(isset($xiaohaoMoney['money'])&&$xiaohaoMoney['money']!==NULL){
                $v['e_money']=round($xiaohaoMoney['money'],2);
                $v['e_zhuanhua']=$xiaohaoMoney['transform_val'];
                $v['e_clue_num']=$xiaohaoMoney['clue_num'];
                $v['e_true_consume']=$xiaohaoMoney['true_consume'];
//                $v['e_chengben']=0;
//                $v['jinxiansunhao_rate']=1*100;//进线损耗率
//                $v['all_push_rate']=0;//总损耗率
                if($xiaohaoMoney['transform_val']>0){
                    $v['e_chengben']=bcdiv($xiaohaoMoney['money'],$xiaohaoMoney['transform_val'],2);
                    $v['jinxiansunhao_rate']=bcsub(1,bcdiv($v['clue_num'],$xiaohaoMoney['transform_val'],4),4)*100;
                    $v['all_push_rate']=bcsub(1,bcdiv($v['push_num'],$xiaohaoMoney['transform_val'],4),4)*100;//总损耗率
                }
                if($v['clue_num']>0){
                    //真实成本
                    $v['avg_api_clue']=bcdiv($xiaohaoMoney['true_consume'],$v['clue_num'],2);
                }else{
                    $v['avg_api_clue']=0;
                }
                //分发客单
//                if($v['push_num']>0){
//                    $v['e_push_avg_money']=bcdiv($xiaohaoMoney['money'],$v['push_num'],2);
//                }else{
//                    $v['e_push_avg_money']=0;
//                }
                //收发总差额(分发总金额-(账户消耗*(1-返点)))，
//                $temp1=bcmul($xiaohaoMoney['money'],bcsub(1,$v['clue_rate']/100,3),2);
                $v['e_sf_all_cha']=round(bcsub($v['price'],$v['e_true_consume'],3),2);
                //收发客单差额
                $v['e_sf_avg_sub']=round(bcsub($v['push_avg_money'],$v['avg_api_clue'],3),2);

            }else{
                $v['e_sf_all_cha']=$v['price'];
            }

            if($v['price']>0){
                $v['maoli_rate']=bcdiv($v['e_sf_all_cha'],$v['price'],4)*100;
            }else{
                $v['maoli_rate']=0;
            }

            $nowXiaoHao+=floatval($v['e_money']); //导入的消耗
            $nowZhuanghua+=intval($v['e_zhuanhua']); //导入的转换量
            $apiJinXianXiaohao+=floatval($v['e_true_consume']);//导入计算的真实消耗
            $apiJixXianNum+=$v['clue_num'];
            $nowFenfaNum+=$v['push_num'];
            $reClueNum+=$v['re_clue_num'];
            $rePhone+=$v['re_phone'];
            $reCity+=$v['re_clue_city'];
            $pushMoney+=$v['price'];
            $nowShouFaAllCha+=floatval($v['e_sf_all_cha']);

        }

        $sort = array_column($infos, 'account_id');
// 按照sort字段升序  其中SORT_ASC表示升序 SORT_DESC表示降序
        array_multisort($sort, SORT_ASC, $infos);

//        dump($infos);

        if($nowZhuanghua>0){
            $nowChengBen=bcdiv($nowXiaoHao,$nowZhuanghua,2); //成本=消耗/转化
            $jinXianShunHaoRate=bcsub(1,bcdiv($apiJixXianNum,$nowZhuanghua,4),4)*100;
            $allShunHaoRare=bcsub(1,bcdiv($nowFenfaNum,$nowZhuanghua,4),4)*100;
        }else{
            $jinXianShunHaoRate='-';
            $allShunHaoRare='-';
            $nowChengBen='-';
        }
        if($apiJixXianNum>0){
            $apiJinXianChengBen=bcdiv($apiJinXianXiaohao,$apiJixXianNum,2);
            $fenFaShunHaoRare=bcsub(1,bcdiv($nowFenfaNum,$apiJixXianNum,4),4)*100;
            $reClueRate=bcdiv($reClueNum,$apiJixXianNum,4)*100;
            $rePhoneRate=bcdiv($rePhone,$apiJixXianNum,4)*100;
            $reCityRate=bcdiv($reCity,$apiJixXianNum,4)*100;
        }else{
            $apiJinXianChengBen='-';
            $fenFaShunHaoRare='-';
            $reClueRate='-';
            $rePhoneRate='-';
            $reCityRate='-';
        }

        if($nowFenfaNum>0){
            $pushAvgMoney=bcdiv($pushMoney,$nowFenfaNum,2);
        }else{
            $pushAvgMoney=0;
        }

        $nowKeDanCha=bcsub($pushAvgMoney,$nowChengBen,2);

        if($pushMoney>0){
            $maoliRate=bcdiv($nowShouFaAllCha,$pushMoney,4)*100;
        }else{
            $maoliRate=0;
        }

        $youhua = model('AuthGroupAccess')->alias('a')
            ->field('a.*,b.phone,b.realname')
            ->join('admin b','a.uid=b.id')
            ->where(function ($q){
                $q->where('a.group_id',7);
            })
//        ->where('a.group_id',4)
            ->where('b.delete_time',NULL)->select();


        return $this->fetch('',[
            'groupType' => $groupType,
            'channels' => $channels,
            'products' => $products,
            'infos' => $infos,
            'times' => $times,
            'acc_name' => $acc_name,
            'cityname' => $cityname,
            'adv_id' => $adv_id,
            'pro_id' => $pro_id,
            'channel_id' => $channel_id,
            'nowXiaoHao' => $nowXiaoHao,
            'nowZhuanghua' => $nowZhuanghua,
            'apiJinXianXiaohao' => $apiJinXianXiaohao,
            'apiJixXianNum' => $apiJixXianNum,
            'nowFenfaNum' => $nowFenfaNum,
            'reClueNum' => $reClueNum,
            'reClueRate' => $reClueRate,
            'rePhone' => $rePhone,
            'rePhoneRate' => $rePhoneRate,
            'reCity' => $reCity,
            'reCityRate' => $reCityRate,
            'pushMoney' => $pushMoney,
            'nowShouFaAllCha' => $nowShouFaAllCha,
            'nowChengBen' => $nowChengBen,
            'jinXianShunHaoRate' => $jinXianShunHaoRate,
            'allShunHaoRare' => $allShunHaoRare,
            'apiJinXianChengBen' => $apiJinXianChengBen,
            'fenFaShunHaoRare' => $fenFaShunHaoRare,
            'pushAvgMoney' => $pushAvgMoney,
            'nowKeDanCha' => $nowKeDanCha,
            'showTime' => $showTime,
            'row' => $row,
            'maoliRate' => $maoliRate,
            'youhua' => $youhua,
            'youhua_id' => $youhua_id,
            'group_id' => $this->group_id,
            'is_kong' => $isKong,
        ]);
    }

    //账户投放统计导出
    public function accountClueTotalAccountExport(){
        $times = input('times','');
        $acc_name = input('acc_name','');
        $adv_id = input('adv_id','');
        $pro_id = input('pro_id','');
        $channel_id = input('channel_id',0);
        $groupType = input('gtype',1);//1账户维度2城市维度3商户维度
        $cityname = input('cityname','');
        $youhua_id = input('youhua_id','');


        $map = [];
        $map1 = [];
        $map3 = [];
        $map4 = [];


        if($groupType==1){
            if($adv_id){
                $map['a.adv_id']=$adv_id;
                $map4['b.adv_id']=$adv_id;
            }
            if($acc_name != ''){
                $mapa['acc_name'] = ['like','%'.$acc_name.'%'];
                $account=model('account')->where($mapa)->value('adv_id');
                if($account){
                    $map['a.adv_id']=$account;
                    $map4['b.adv_id']=$account;
                }else{
                    $map['a.adv_id']=0;
                    $map4['b.adv_id']=0;
                }
            }

            if($channel_id != 0){
                $map['a.type'] = $channel_id;
                $map4['b.channel_id'] = $channel_id;
            }
        }else{
            //模糊查询城市
            if(!empty($cityname)){
                $cityIdArr=Db::name('city')->field('id,type,pid')->where('name','like',$cityname."%")->where('type',2)->find();
                if($cityIdArr){
                    $map['a.city']=$cityIdArr['pid'].'-'.$cityIdArr['id'];
                }else{
                    $map['a.city']=0;
                }
            }
        }

        if($youhua_id!=''){
            $map['bind_youhua']=$youhua_id;
            $map4['bind_youhua']=$youhua_id;
        }

        if($pro_id!=''){
            $map['a.pro_id']=$pro_id;
            $map4['a.pro_id']=$pro_id;
        }

        if($times != ''){
            $time_arr = explode(' - ',$times);
            $showTime=$times;
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
            $map2['time'] = ['between',[$time_arr[0],$time_arr[1]]];
        }else{
            $times=date('Y-m-d').' 00:00:00 - '.date('Y-m-d').' 23:59:59';
            $showTime=date('Y-m-d').' - '.date('Y-m-d');
            $time_arr = explode(' - ',$times);
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
            $map2['time'] = ['between',[$time_arr[0],$time_arr[1]]];
        }

        $orderDy='a.id desc';

        $channels=$this->getChannelArray();
        $productArr = Db::name('product')->column('name','id');
        $products = model('Product')->where('status',1)->column('name','id');

        if($groupType==1){

            $accountArr=Db::name('clue_cost_log')->alias('a')->field('b.*,a.account_id,a.pro_id')
                ->join('account b','b.id=a.account_id','Left')
                ->where($map4)->where($map2)->group('account_id,pro_id')->select();

            $group='a.adv_id,a.pro_id';
            $infos=model('Clue')->alias('a')->field('c.clue_rate,count(a.id) clue_num,sum(b.price) price,a.adv_id,a.pro_id,a.type,c.acc_name,c.acc_desc,show_channel,c.id account_id,SUM(IF(a.status = 1,1,0)) push_num')
                ->join('clue_log b','b.clue_id=a.id','LEFT')
                ->join('account c','c.adv_id=a.adv_id','LEFT')
                ->where($map)->group($group)->order($orderDy)->select();


            foreach ($accountArr as $v){
                $isPro=true;
                $proType=$v['pro_id'];
                foreach ($infos as $vv){
                    if($v['account_id']==$vv['account_id']&&$v['pro_id']==$vv['pro_id']){
                        $isPro=false;
                    }
                }
                if($isPro){//没有这个商户
                    $infos[]=[
                        'account_id'=>$v['id'],
                        'clue_rate'=>$v['clue_rate'],
                        'clue_num'=>0,
                        'price'=>0,
                        'adv_id'=>$v['adv_id'],
                        'pro_id'=>$proType,
                        'type'=>$v['channel_id'],
                        'acc_name'=>$v['acc_name'],
                        'acc_desc'=>$v['acc_desc'],
                        'show_channel'=>$v['show_channel'],
                        'push_num'=>0,
                    ];
                }
            }
        }elseif($groupType==2){
            $group='a.city,a.pro_id';
            $infos=model('Clue')->alias('a')->field('a.city,c.clue_rate,count(a.id) clue_num,sum(b.price) price,a.adv_id,a.pro_id,a.type,c.acc_name,c.acc_desc,show_channel,c.id account_id,SUM(IF(a.status = 1,1,0)) push_num')
                ->join('clue_log b','b.clue_id=a.id','LEFT')
                ->join('account c','c.adv_id=a.adv_id','LEFT')
                ->where($map)->group($group)->order($orderDy)->paginate();
        }elseif ($groupType==3){
            $group='b.mer_id,a.pro_id';
            $infos=model('Clue')->alias('a')->field('mer_name,mer_desc,username,d.phone mer_phone,a.city,c.clue_rate,count(a.id) clue_num,sum(b.price) price,a.adv_id,a.pro_id,a.type,c.acc_name,c.acc_desc,show_channel,c.id account_id,SUM(IF(a.status = 1,1,0)) push_num')
                ->join('clue_log b','b.clue_id=a.id','LEFT')
                ->join('account c','c.adv_id=a.adv_id','LEFT')
                ->join('merchant d','d.id=b.mer_id','LEFT')
                ->where($map)->group($group)->order($orderDy)->paginate();
        }else{
            $infos=[];
        }

        if(empty($infos)){
            echo '没有数据导出';
        }

        foreach ($infos as &$v){
            $v['shop_name']='未知';
            $v['shop_desc']='未知';
            $v['shop_username']='未知';
            $v['shop_phone']='未知';

            $v['pro_name']=$productArr[$v['pro_id']];
            //账户消耗
            $v['city_name']='未知';
            $v['channel_name']='';
            $v['acc']='';
            if($groupType==1){
                $v['channel_name']=$channels[$v['type']];
                $map3=['account_id'=>$v['account_id'],'pro_id'=>$v['pro_id']];
            }elseif($groupType==2){
                $map3=['city'=>$v['city'],'pro_id'=>$v['pro_id']];
                if($v['city'] != ''){
                    $ids = explode('-',$v['city']);
                    $cityNum=count($ids);
                    if($cityNum==1){
                        $v['city_name'] = $this->getCityName($ids[0]);
                    }elseif ($cityNum==2){
                        $prov = $this->getCityName($ids[0]);
                        $city = $this->getCityName($ids[1]);
                        if($prov == $city){
                            $v['city_name'] = $prov;
                        }else{
                            $v['city_name'] = $prov.'-'.$city;
                        }
                    }
                }
            }elseif($groupType==3){
                $v['shop_name']=$v['mer_name'];
                $v['shop_desc']=$v['mer_desc'];
                $v['shop_username']=$v['username'];
                $v['shop_phone']=$v['mer_phone'];
            }
            $xiaohaoMoney=Db::name('clue_cost_log')->field('count(id) log_num,sum(true_consume) true_consume,sum(money) money,sum(transform_val) transform_val,sum(clue_num) clue_num')->where($map3)->where($map2)->find();
            $v['e_money']='-';//账户消耗
            $v['e_zhuanhua']='-';//账户转化量
            $v['e_clue_num']='-';//导入进线量
            $v['e_chengben']='-';//成本计算
            $v['e_true_consume']='-';//Api进线消耗（真实消耗）
            $v['jinxiansunhao_rate']='-';//进线损耗率
            $v['avg_api_clue']='-';//真实成本

            $v['log_num']=empty($xiaohaoMoney['log_num'])?0:$xiaohaoMoney['log_num'];

            if($v['clue_num']>0){
                //分发损耗率
                $v['push_rate']=bcsub(1,bcdiv($v['push_num'],$v['clue_num'],4),4)*100;
                $v['all_push_rate']='-';//总损耗率
                //重复
                $v['re_clue_num']=Db::name('clue_err_log')->alias('a')->where(['type'=>1,'adv_id'=>$v['adv_id'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_clue_rate']=bcdiv($v['re_clue_num'],$v['clue_num'],4)*100;
                //撞库
                $v['re_phone']=Db::name('clue_err_log')->alias('a')
//                    ->join('clue b','a.clue_id=b.id AND b.status=0')
                    ->where(['a.type'=>2,'a.adv_id'=>$v['adv_id'],'a.pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_phone_rate']=bcdiv($v['re_phone'],$v['clue_num'],4)*100;
                //非目标城市
                $v['re_clue_city']=Db::name('clue_err_log')->alias('a')
//                    ->join('clue b','a.clue_id=b.id AND b.status=0')
                    ->where(['a.type'=>3,'a.adv_id'=>$v['adv_id'],'a.pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_city_rate']=bcdiv($v['re_clue_city'],$v['clue_num'],4)*100;
            }else{
                $v['push_rate']='-';
                $v['all_push_rate']='-';//总损耗率
                //重复
                $v['re_clue_num']=Db::name('clue_err_log')->alias('a')->where(['type'=>1,'adv_id'=>$v['adv_id'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_clue_rate']='-';
                //撞库
                $v['re_phone']=Db::name('clue_err_log')->alias('a')->where(['type'=>2,'adv_id'=>$v['adv_id'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_phone_rate']='-';
                //非目标城市
                $v['re_clue_city']=Db::name('clue_err_log')->alias('a')->where(['type'=>3,'adv_id'=>$v['adv_id'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_city_rate']='-';
            }

            //分发客单
            if($v['push_num']>0){
                $v['push_avg_money']=bcdiv($v['price'],$v['push_num'],2);
            }else{
                $v['push_avg_money']=0;
            }

            $v['e_push_avg_money']='-';

            //收发总差额(分发总金额-(账户消耗*(1-返点)))，
            $v['e_sf_all_cha']='-';
//            $temp1=bcmul($v['price'],bcsub(1,$v['clue_rate']/100,3),2);
//            $v['sf_all_cha']=bcsub($v['price'],$temp1,2);
            //收发客单差额
            $v['e_sf_avg_sub']='-';

            if(isset($xiaohaoMoney['money'])&&$xiaohaoMoney['money']!==NULL){
                $v['e_money']=round($xiaohaoMoney['money'],2);
                $v['e_zhuanhua']=$xiaohaoMoney['transform_val'];
                $v['e_clue_num']=$xiaohaoMoney['clue_num'];
                $v['e_true_consume']=$xiaohaoMoney['true_consume'];
//                $v['e_chengben']=0;
//                $v['jinxiansunhao_rate']=1*100;//进线损耗率
//                $v['all_push_rate']=0;//总损耗率
                if($xiaohaoMoney['transform_val']>0){
                    $v['e_chengben']=bcdiv($xiaohaoMoney['money'],$xiaohaoMoney['transform_val'],2);
                    $v['jinxiansunhao_rate']=bcsub(1,bcdiv($v['clue_num'],$xiaohaoMoney['transform_val'],4),4)*100;
                    $v['all_push_rate']=bcsub(1,bcdiv($v['push_num'],$xiaohaoMoney['transform_val'],4),4)*100;//总损耗率
                }
                if($v['clue_num']>0){
                    //真实成本
                    $v['avg_api_clue']=bcdiv($xiaohaoMoney['true_consume'],$v['clue_num'],2);
                }else{
                    $v['avg_api_clue']=0;
                }
                //分发客单
//                if($v['push_num']>0){
//                    $v['e_push_avg_money']=bcdiv($xiaohaoMoney['money'],$v['push_num'],2);
//                }else{
//                    $v['e_push_avg_money']=0;
//                }
                //收发总差额(分发总金额-(账户消耗*(1-返点)))，
//                $temp1=bcmul($xiaohaoMoney['money'],bcsub(1,$v['clue_rate']/100,3),2);
                $v['e_sf_all_cha']=round(bcsub($v['price'],$v['e_true_consume'],3),2);
                //收发客单差额
                $v['e_sf_avg_sub']=round(bcsub($v['push_avg_money'],$v['avg_api_clue'],3),2);

            }else{
                $v['e_sf_all_cha']=$v['price'];
            }


        }

        $sort = array_column($infos, 'account_id');
// 按照sort字段升序  其中SORT_ASC表示升序 SORT_DESC表示降序
        array_multisort($sort, SORT_ASC, $infos);

        $newExcel = new Spreadsheet();
//获取当前操作sheet的对象
        $objSheet = $newExcel->getActiveSheet();
        $date = date('YmdHis',time());
        $name = '账户投放统计';
        $objSheet->setTitle($name);//设置当前sheet的标题
//设置表头

        $objSheet->setCellValue('A1', '渠道')
            ->setCellValue('B1', '账户')
            ->setCellValue('C1', '产品')
            ->setCellValue('D1', '账户消耗')
            ->setCellValue('E1', '账户转化量')
            ->setCellValue('F1', '账户成本')
            ->setCellValue('G1', 'API进线消耗')
            ->setCellValue('H1', 'API进线量')
            ->setCellValue('I1', '进线损耗率')
            ->setCellValue('J1', '真实成本')
            ->setCellValue('K1', '分发成功量')
            ->setCellValue('L1', '分发损耗率')
            ->setCellValue('M1', '总损耗率')
            ->setCellValue('N1', '重复量')
            ->setCellValue('O1', '重复率')
            ->setCellValue('P1', '非目标城市')
            ->setCellValue('Q1', '非目标城市率')
            ->setCellValue('R1', '撞库量')
            ->setCellValue('S1', '撞库率')
            ->setCellValue('T1', '分发总金额')
            ->setCellValue('U1', '分发客单')
            ->setCellValue('V1', '收发总差额')
            ->setCellValue('W1', '收发客单差额')
            ->setCellValue('X1', '导入记录数量')
        ;

        $objSheet->getColumnDimension('A')->setWidth(12);
        $objSheet->getColumnDimension('B')->setWidth(20);
        $objSheet->getColumnDimension('C')->setWidth(10);
        $objSheet->getColumnDimension('D')->setWidth(12);
        $objSheet->getColumnDimension('E')->setWidth(12);
        $objSheet->getColumnDimension('F')->setWidth(12);
        $objSheet->getColumnDimension('G')->setWidth(12);
        $objSheet->getColumnDimension('H')->setWidth(12);
        $objSheet->getColumnDimension('I')->setWidth(12);
        $objSheet->getColumnDimension('J')->setWidth(12);
        $objSheet->getColumnDimension('K')->setWidth(12);
        $objSheet->getColumnDimension('L')->setWidth(12);
        $objSheet->getColumnDimension('M')->setWidth(12);
        $objSheet->getColumnDimension('N')->setWidth(12);
        $objSheet->getColumnDimension('O')->setWidth(12);
        $objSheet->getColumnDimension('P')->setWidth(12);
        $objSheet->getColumnDimension('Q')->setWidth(12);
        $objSheet->getColumnDimension('R')->setWidth(12);
        $objSheet->getColumnDimension('S')->setWidth(12);
        $objSheet->getColumnDimension('T')->setWidth(12);
        $objSheet->getColumnDimension('U')->setWidth(12);
        $objSheet->getColumnDimension('V')->setWidth(12);
        $objSheet->getColumnDimension('W')->setWidth(12);
        $objSheet->getColumnDimension('X')->setWidth(12);

        $info = $infos;
        $dataCount = count($info);
        $k = 1;
        if($dataCount == 0){
            exit;
        }else {
            for ($i = 0; $i < $dataCount; $i++) {
                $k = $k + 1;
                $objSheet->setCellValue('A' . $k, $info[$i]['channel_name'])
                    ->setCellValue('B' . $k, $info[$i]['acc_name'])
                    ->setCellValue('C' . $k, $info[$i]['pro_name'])
                    ->setCellValue('D' . $k, $info[$i]['e_money'])
                    ->setCellValue('E' . $k, $info[$i]['e_zhuanhua'])
                    ->setCellValue('F' . $k, $info[$i]['e_chengben'])
                    ->setCellValue('G' . $k, $info[$i]['e_true_consume'])
                    ->setCellValue('H' . $k, $info[$i]['clue_num'])
                    ->setCellValue('I' . $k, $info[$i]['jinxiansunhao_rate'].'%')
                    ->setCellValue('J' . $k, $info[$i]['avg_api_clue'])
                    ->setCellValue('K' . $k, $info[$i]['push_num'])
                    ->setCellValue('L' . $k, $info[$i]['push_rate'].'%')
                    ->setCellValue('M' . $k, $info[$i]['all_push_rate'].'%')
                    ->setCellValue('N' . $k, $info[$i]['re_clue_num'])
                    ->setCellValue('O' . $k, $info[$i]['re_clue_rate'].'%')
                    ->setCellValue('P' . $k, $info[$i]['re_clue_city'])
                    ->setCellValue('Q' . $k, $info[$i]['re_city_rate'].'%')
                    ->setCellValue('R' . $k, $info[$i]['re_phone'])
                    ->setCellValue('S' . $k, $info[$i]['re_phone_rate'].'%')
                    ->setCellValue('T' . $k, $info[$i]['price'])
                    ->setCellValue('U' . $k, $info[$i]['push_avg_money'])
                    ->setCellValue('V' . $k, $info[$i]['e_sf_all_cha'])
                    ->setCellValue('W' . $k, $info[$i]['e_sf_avg_sub'])
                    ->setCellValue('X' . $k, $info[$i]['log_num'])
                ;
            }
        }

        $filename = $name.$date;
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename='.$filename.'.xlsx');
        header('Cache-Control: max-age=0');
//
        $write = IOFactory::createWriter($newExcel, 'Xlsx');
        $write->save('php://output');

    }


    //商户投放统计
    public function accountClueTotalMerchant(){
        $times = input('times','');
        $mer_name = input('mer_name','');
        $pro_id = input('pro_id','');
        $cityname = input('cityname','');

        $map = [];
        $map1 = [];//撞库
        $map3 = [];

        //模糊查询城市
        if(!empty($mer_name)){
            $merIdArr=model('Merchant')->where('mer_name','like','%'.$mer_name."%")->column('id');
            if($merIdArr){
                $map['a.mer_id']=['in',$merIdArr];
            }else{
                $map['a.mer_id']=0;
            }
        }

        //模糊查询城市
        if(!empty($cityname)){
            $cityIdArr=Db::name('city')->field('id,type,pid')->where('name','like',$cityname."%")->where('type',2)->find();
            if($cityIdArr){
                $map['a.city']=$cityIdArr['pid'].'-'.$cityIdArr['id'];
            }else{
                $map['a.city']=0;
            }
        }

        if($pro_id!=''){
            $map['a.pro_id']=$pro_id;
        }

        if($times != ''){
            $time_arr = explode(' - ',$times);
            $showTime=$times;
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
            $map2['time'] = ['between',[$time_arr[0],$time_arr[1]]];
        }else{
            $times=date('Y-m-d').' 00:00:00 - '.date('Y-m-d').' 23:59:59';
            $showTime=date('Y-m-d').' - '.date('Y-m-d');
            $time_arr = explode(' - ',$times);
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
            $map2['time'] = ['between',[$time_arr[0],$time_arr[1]]];
        }

        $orderDy='b.id desc';

        $productArr = Db::name('product')->column('name','id');
        $products = model('Product')->where('status',1)->column('name','id');


        $group='a.mer_id,a.pro_id,a.city';
        $infos=model('clue_log')->alias('a')->field("a.mer_id,count(a.id) num,a.city,a.pro_id,sum(a.price) price,b.mer_name,b.phone mer_phone,username,b.balance,sum(c.price) cost_price")
            ->join('merchant b','b.id=a.mer_id')
            ->join('clue c','a.clue_id=c.id')
            ->where($map)->group($group)->order($orderDy)->paginate();

        //统计

        $total=model('clue_log')->alias('a')->field("count(a.id) num,sum(a.price) price,sum(c.price) cost_price")
            ->join('clue c','a.clue_id=c.id')
            ->where($map)->find();


        $errPushTotal=Db::name('clue_err_push_log')->alias('a')->field('count(a.id) num,sum(b.price) cost_price')
            ->join('clue b','a.clue_id=b.id')
            ->where($map)->find();


        $totalRePhone=Db::name('clue_err_log')->alias('a')
            ->join('clue b','a.clue_id=b.id AND b.status=0')
            ->where(['a.type'=>2])->where($map)->count();





        $allExecuteNum=!empty($errPushTotal['num'])?$errPushTotal['num']:0;
        $allFenfaNum=!empty($total['num'])?$total['num']:0;
        $shunHaoRate=0;
        $fenfaAllCostMoney=!empty($errPushTotal['cost_price'])?$errPushTotal['cost_price']:0;
        $fenfaSuccessMoney=!empty($total['cost_price'])?$total['cost_price']:0;
        $shunhaoMoney=0;
        $costPrice=0;
        $allJieSuan=!empty($total['price'])?$total['price']:0;
        $jiesuanPirce=0;
        $zhuangkuNum=$totalRePhone;
        $zhuangkuRate=0;
        $lirun=bcsub($allJieSuan,$fenfaSuccessMoney,2);

        if($allExecuteNum>0){
            $zhuangkuRate=bcdiv($totalRePhone,$allExecuteNum,4)*100;
        }else{
            $zhuangkuRate=0;
        }

//        $allExecuteNum=0;
//        $allFenfaNum=0;
//        $shunHaoRate=0;
//        $fenfaAllCostMoney=0;
//        $fenfaSuccessMoney=0;
//        $shunhaoMoney=0;
//        $costPrice=0;
//        $allJieSuan=0;
//        $jiesuanPirce=0;
//        $zhuangkuNum=0;
//        $zhuangkuRate=0;
//        $lirun=0;


        foreach ($infos as &$v){

            $v['pro_name']=$productArr[$v['pro_id']];

            $errPush=Db::name('clue_err_push_log')->alias('a')
                ->field('count(a.id) num,sum(b.price) cost_price')
                ->join('clue b','a.clue_id=b.id')
                ->where(['a.mer_id'=>$v['mer_id'],'a.pro_id'=>$v['pro_id'],'a.city'=>$v['city']])->where($map1)->find();

            $v['execute_num']=!empty($errPush['num'])?$errPush['num']:0;//分发执行量
            $v['execute_money']=!empty($errPush['cost_price'])?$errPush['cost_price']:0;//分发执行总成本

            //分发损耗率【1-分发成功量/分发执行量】
            if($v['execute_num']>0){
                $rate1=bcdiv($v['num'],$v['execute_num'],4);
                $v['fenfa_shunhao_rate']=bcsub(1,$rate1,4)*100;
            }else{
                $v['fenfa_shunhao_rate']=0;
            }

            //损耗金额【分发执行总成本-分发成功总成本】
            $v['shunhao_money']=bcsub($v['execute_money'],$v['cost_price'],2);
            //成本单价【分发成功总成本/分发成功量】
            $v['chengben_avg']=bcdiv($v['cost_price'],$v['num'],2);
            //总结算额【商户扣了多少钱】

            //结算单价【分发总结算/分发成功量】
            $v['jiesuan_avg']=bcdiv($v['price'],$v['num'],2);

            $v['re_phone']=Db::name('clue_err_log')->alias('a')
                ->join('clue b','a.clue_id=b.id AND b.status=0')
                ->where(['a.type'=>2,'a.mer_id'=>$v['mer_id'],'a.city'=>$v['city'],'a.pro_id'=>$v['pro_id']])->where($map1)->count();
            if($v['execute_num']>0){
                $v['re_phone_rate']=bcdiv($v['re_phone'],$v['execute_num'],4)*100;
            }else{
                $v['re_phone_rate']=0;
            }
            //毛利【分发成功总成本-总结算额】
            $v['lirun']=bcsub($v['price'],$v['cost_price'],2);

//            $allExecuteNum+=$v['execute_num'];
//            $allFenfaNum+=$v['num'];
//            $fenfaAllCostMoney=bcadd($fenfaAllCostMoney,$v['execute_money'],2);
//            $fenfaSuccessMoney=bcadd($fenfaSuccessMoney,$v['cost_price'],2);
//            $allJieSuan=bcadd($allJieSuan,$v['price'],2);
//            $zhuangkuNum+=$v['re_phone'];
//            $lirun=bcadd($lirun,$v['lirun'],2);

            if($v['city'] != ''){
                $ids = explode('-',$v['city']);
                $cityNum=count($ids);
                if($cityNum==1){
                    $v['city'] = $this->getCityName($ids[0]);
                }elseif ($cityNum==2){
                    $prov = $this->getCityName($ids[0]);
                    $city = $this->getCityName($ids[1]);
                    if($prov == $city){
                        $v['city'] = $prov;
                    }else{
                        $v['city'] = $prov.'-'.$city;
                    }
                }
            }


        }

//        dump($infos);die;
        if($allExecuteNum>0){
            $shunHao=bcdiv($allFenfaNum,$allExecuteNum,4);
            $shunHaoRate=bcsub(1,$shunHao,4);
            $zhuangkuRate=bcdiv($zhuangkuNum,$allExecuteNum,4);
        }
        $shunhaoMoney=bcsub($fenfaAllCostMoney,$fenfaSuccessMoney,2);
        if($allFenfaNum>0){

            $costPrice=bcdiv($fenfaSuccessMoney,$allFenfaNum,2);
            $jiesuanPirce=bcdiv($allJieSuan,$allFenfaNum,2);
        }

//        dump($infos);die;

        return $this->fetch('',[
            'infos' => $infos,
            'products' => $products,
            'times' => $times,
            'cityname' => $cityname,
            'pro_id' => $pro_id,
            'mer_name' => $mer_name,
            'allExecuteNum' => $allExecuteNum,
            'allFenfaNum' => $allFenfaNum,
            'shunHaoRate' => $shunHaoRate,
            'fenfaAllCostMoney' => $fenfaAllCostMoney,
            'fenfaSuccessMoney' => $fenfaSuccessMoney,
            'shunhaoMoney' => $shunhaoMoney,
            'costPrice' => $costPrice,
            'allJieSuan' => $allJieSuan,
            'jiesuanPirce' => $jiesuanPirce,
            'zhuangkuNum' => $zhuangkuNum,
            'zhuangkuRate' => $zhuangkuRate,
            'lirun' => $lirun,
            'showTime' => $showTime,
        ]);
    }

    //商户投放统计
    public function accountClueTotalMerchantExport(){
        $times = input('times','');
        $mer_name = input('mer_name','');
        $pro_id = input('pro_id','');
        $cityname = input('cityname','');

        $map = [];
        $map1 = [];//撞库
        $map3 = [];

        //模糊查询城市
        if(!empty($mer_name)){
            $merIdArr=model('Merchant')->where('mer_name','like','%'.$mer_name."%")->column('id');
            if($merIdArr){
                $map['a.mer_id']=['in',$merIdArr];
            }else{
                $map['a.mer_id']=0;
            }
        }

        //模糊查询城市
        if(!empty($cityname)){
            $cityIdArr=Db::name('city')->field('id,type,pid')->where('name','like',$cityname."%")->where('type',2)->find();
            if($cityIdArr){
                $map['a.city']=$cityIdArr['pid'].'-'.$cityIdArr['id'];
            }else{
                $map['a.city']=0;
            }
        }

        if($pro_id!=''){
            $map['a.pro_id']=$pro_id;
        }

        if($times != ''){
            $time_arr = explode(' - ',$times);
            $showTime=$times;
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
            $map2['time'] = ['between',[$time_arr[0],$time_arr[1]]];
        }else{
            $times=date('Y-m-d').' 00:00:00 - '.date('Y-m-d').' 23:59:59';
            $showTime=date('Y-m-d').' - '.date('Y-m-d');
            $time_arr = explode(' - ',$times);
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
            $map2['time'] = ['between',[$time_arr[0],$time_arr[1]]];
        }

        $orderDy='b.id desc';

        $productArr = Db::name('product')->column('name','id');
        $products = model('Product')->where('status',1)->column('name','id');


        $group='a.mer_id,a.pro_id,a.city';
        $infos=model('clue_log')->alias('a')->field("a.mer_id,count(a.id) num,a.city,a.pro_id,sum(a.price) price,b.mer_name,b.phone mer_phone,username,b.balance,sum(c.price) cost_price")
            ->join('merchant b','b.id=a.mer_id')
            ->join('clue c','a.clue_id=c.id')
            ->where($map)->group($group)->order($orderDy)->select();

        if(empty($infos)){
            echo '没有数据导出';
        }

        foreach ($infos as &$v){

            $v['pro_name']=$productArr[$v['pro_id']];

            $errPush=Db::name('clue_err_push_log')->alias('a')
                ->field('count(a.id) num,sum(b.price) cost_price')
                ->join('clue b','a.clue_id=b.id')
                ->where(['a.mer_id'=>$v['mer_id'],'a.pro_id'=>$v['pro_id'],'a.city'=>$v['city']])->where($map1)->find();

            $v['execute_num']=!empty($errPush['num'])?$errPush['num']:0;//分发执行量
            $v['execute_money']=!empty($errPush['cost_price'])?$errPush['cost_price']:0;//分发执行总成本

            //分发损耗率【1-分发成功量/分发执行量】
            if($v['execute_num']>0){
                $rate1=bcdiv($v['num'],$v['execute_num'],4);
                $v['fenfa_shunhao_rate']=bcsub(1,$rate1,4)*100;
            }else{
                $v['fenfa_shunhao_rate']=0;
            }

            //损耗金额【分发执行总成本-分发成功总成本】
            $v['shunhao_money']=bcsub($v['execute_money'],$v['cost_price'],2);
            //成本单价【分发成功总成本/分发成功量】
            $v['chengben_avg']=bcdiv($v['cost_price'],$v['num'],2);
            //总结算额【商户扣了多少钱】

            //结算单价【分发总结算/分发成功量】
            $v['jiesuan_avg']=bcdiv($v['price'],$v['num'],2);

            $v['re_phone']=Db::name('clue_err_log')->alias('a')
                ->join('clue b','a.clue_id=b.id AND b.status=0')
                ->where(['a.type'=>2,'a.mer_id'=>$v['mer_id'],'a.city'=>$v['city'],'a.pro_id'=>$v['pro_id']])->where($map1)->count();
            if($v['execute_num']>0){
                $v['re_phone_rate']=bcdiv($v['re_phone'],$v['execute_num'],4)*100;
            }else{
                $v['re_phone_rate']=0;
            }
            //毛利【分发成功总成本-总结算额】
            $v['lirun']=bcsub($v['price'],$v['cost_price'],2);

//            $allExecuteNum+=$v['execute_num'];
//            $allFenfaNum+=$v['num'];
//            $fenfaAllCostMoney=bcadd($fenfaAllCostMoney,$v['execute_money'],2);
//            $fenfaSuccessMoney=bcadd($fenfaSuccessMoney,$v['cost_price'],2);
//            $allJieSuan=bcadd($allJieSuan,$v['price'],2);
//            $zhuangkuNum+=$v['re_phone'];
//            $lirun=bcadd($lirun,$v['lirun'],2);

            if($v['city'] != ''){
                $ids = explode('-',$v['city']);
                $cityNum=count($ids);
                if($cityNum==1){
                    $v['city'] = $this->getCityName($ids[0]);
                }elseif ($cityNum==2){
                    $prov = $this->getCityName($ids[0]);
                    $city = $this->getCityName($ids[1]);
                    if($prov == $city){
                        $v['city'] = $prov;
                    }else{
                        $v['city'] = $prov.'-'.$city;
                    }
                }
            }

        }

        $newExcel = new Spreadsheet();
//获取当前操作sheet的对象
        $objSheet = $newExcel->getActiveSheet();
        $date = date('YmdHis',time());
        $name = '商户投放统计';
        $objSheet->setTitle($name);//设置当前sheet的标题
//设置表头


        $objSheet->setCellValue('A1', '商户名称')
            ->setCellValue('B1', '城市')
            ->setCellValue('C1', '产品')
            ->setCellValue('D1', '分发执行量')
            ->setCellValue('E1', '分发成功量')
            ->setCellValue('F1', '分发损耗率')
            ->setCellValue('G1', '分发执行总成本')
            ->setCellValue('H1', '分发成功总成本')
            ->setCellValue('I1', '损耗金额')
            ->setCellValue('J1', '成本单价')
            ->setCellValue('K1', '总结算额')
            ->setCellValue('L1', '结算单价')
            ->setCellValue('M1', '撞库数')
            ->setCellValue('N1', '撞库率')
            ->setCellValue('O1', '毛利')
            ->setCellValue('P1', '账户币余额')

        ;

        $objSheet->getColumnDimension('A')->setWidth(20);
        $objSheet->getColumnDimension('B')->setWidth(12);
        $objSheet->getColumnDimension('C')->setWidth(10);
        $objSheet->getColumnDimension('D')->setWidth(12);
        $objSheet->getColumnDimension('E')->setWidth(12);
        $objSheet->getColumnDimension('F')->setWidth(12);
        $objSheet->getColumnDimension('G')->setWidth(12);
        $objSheet->getColumnDimension('H')->setWidth(12);
        $objSheet->getColumnDimension('I')->setWidth(12);
        $objSheet->getColumnDimension('J')->setWidth(12);
        $objSheet->getColumnDimension('K')->setWidth(12);
        $objSheet->getColumnDimension('L')->setWidth(12);
        $objSheet->getColumnDimension('M')->setWidth(12);
        $objSheet->getColumnDimension('N')->setWidth(12);
        $objSheet->getColumnDimension('O')->setWidth(12);
        $objSheet->getColumnDimension('P')->setWidth(12);


        $info = $infos;
        $dataCount = count($info);
        $k = 1;
        if($dataCount == 0){
            exit;
        }else {
            for ($i = 0; $i < $dataCount; $i++) {
                $k = $k + 1;

                $objSheet->setCellValue('A' . $k, $info[$i]['mer_name'])
                    ->setCellValue('B' . $k, $info[$i]['city'])
                    ->setCellValue('C' . $k, $info[$i]['pro_name'])
                    ->setCellValue('D' . $k, $info[$i]['execute_num'])
                    ->setCellValue('E' . $k, $info[$i]['num'])
                    ->setCellValue('F' . $k, $info[$i]['fenfa_shunhao_rate'].'%')
                    ->setCellValue('G' . $k, $info[$i]['execute_money'])
                    ->setCellValue('H' . $k, $info[$i]['cost_price'])
                    ->setCellValue('I' . $k, $info[$i]['shunhao_money'])
                    ->setCellValue('J' . $k, $info[$i]['chengben_avg'])
                    ->setCellValue('K' . $k, $info[$i]['price'])
                    ->setCellValue('L' . $k, $info[$i]['jiesuan_avg'])
                    ->setCellValue('M' . $k, $info[$i]['re_phone'])
                    ->setCellValue('N' . $k, $info[$i]['re_phone_rate'].'%')
                    ->setCellValue('O' . $k, $info[$i]['lirun'])
                    ->setCellValue('P' . $k, $info[$i]['balance'])

                ;
            }
        }

        $filename = $name.$date;
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename='.$filename.'.xlsx');
        header('Cache-Control: max-age=0');
//
        $write = IOFactory::createWriter($newExcel, 'Xlsx');
        $write->save('php://output');

    }

    //城市投放统计
    public function accountClueTotal(){
        $times = input('times','');
        $acc_name = input('acc_name','');
        $adv_id = input('adv_id','');
        $pro_id = input('pro_id','');
        $channel_id = input('channel_id',0);
        $groupType = input('gtype',2);//2城市维度
        $cityname = input('cityname','');

        $map = [];
        $map1 = [];
        $map3 = [];



        //模糊查询城市
        if(!empty($cityname)){
            $cityIdArr=Db::name('city')->field('id,type,pid')->where('name','like',$cityname."%")->where('type',2)->find();
            if($cityIdArr){
                $map['a.city']=$cityIdArr['pid'].'-'.$cityIdArr['id'];
            }else{
                $map['a.city']=0;
            }
        }



        if($pro_id!=''){
            $map['a.pro_id']=$pro_id;
        }

        if($times != ''){
            $time_arr = explode(' - ',$times);
            $showTime=$times;
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
            $map2['time'] = ['between',[$time_arr[0],$time_arr[1]]];
        }else{
            $times=date('Y-m-d').' 00:00:00 - '.date('Y-m-d').' 23:59:59';
            $showTime=date('Y-m-d').' - '.date('Y-m-d');
            $time_arr = explode(' - ',$times);
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
            $map2['time'] = ['between',[$time_arr[0],$time_arr[1]]];
        }

        $orderDy='a.id desc';

        $channels=$this->getChannelArray();
        $productArr = Db::name('product')->column('name','id');
        $products = model('Product')->where('status',1)->column('name','id');


        $group='a.city,a.pro_id';
        $infos=model('Clue')->alias('a')->field('a.city,c.clue_rate,count(a.id) clue_num,sum(b.price) price,a.adv_id,a.pro_id,a.type,c.acc_name,c.acc_desc,show_channel,c.id account_id,SUM(IF(a.status = 1,1,0)) push_num')
            ->join('clue_log b','b.clue_id=a.id','LEFT')
            ->join('account c','c.adv_id=a.adv_id','LEFT')
            ->where($map)->group($group)->order($orderDy)->paginate();

        $nowXiaoHao=0; //导入的消耗
        $nowZhuanghua=0; //导入的转换量
        $nowChengBen=0; //成本=消耗/转化
        $apiJinXianXiaohao=0;//导入计算的真实消耗
        $apiJixXianNum=0;
        $jinXianShunHaoRate=0;//1-（API进线量/转化量）
        $apiJinXianChengBen=0;//消耗/API进线量
        $nowFenfaNum=0;
        $fenFaShunHaoRare=0;//1-(分发数量/进线量);
        $allShunHaoRare=0;//1-(推送/转化);
        $reClueNum=0;
        $rePhone=0;
        $reCity=0;
        $pushMoney=0;
        $pushAvgMoney=0;//分发金额/分发数量
        $nowShouFaAllCha=0;
        $nowKeDanCha=0;//分发均价-真实成本;

        foreach ($infos as &$v){
            $v['shop_name']='未知';
            $v['shop_desc']='未知';
            $v['shop_username']='未知';
            $v['shop_phone']='未知';

            $v['pro_name']=$productArr[$v['pro_id']];
            //账户消耗
            $v['city_name']='未知';
            $v['channel_name']='';
            $v['acc']='';
            if($groupType==1){
                $v['channel_name']=$channels[$v['type']];
                $map3=['account_id'=>$v['account_id'],'pro_id'=>$v['pro_id']];
            }elseif($groupType==2){
                $map3=['city'=>$v['city'],'pro_id'=>$v['pro_id']];
                if($v['city'] != ''){
                    $ids = explode('-',$v['city']);
                    $cityNum=count($ids);
                    if($cityNum==1){
                        $v['city_name'] = $this->getCityName($ids[0]);
                    }elseif ($cityNum==2){
                        $prov = $this->getCityName($ids[0]);
                        $city = $this->getCityName($ids[1]);
                        if($prov == $city){
                            $v['city_name'] = $prov;
                        }else{
                            $v['city_name'] = $prov.'-'.$city;
                        }
                    }
                }
            }elseif($groupType==3){
                $v['shop_name']=$v['mer_name'];
                $v['shop_desc']=$v['mer_desc'];
                $v['shop_username']=$v['username'];
                $v['shop_phone']=$v['mer_phone'];
            }
            $xiaohaoMoney=Db::name('clue_cost_log')->field('sum(true_consume) true_consume,sum(money) money,sum(transform_val) transform_val,sum(clue_num) clue_num')->where($map3)->where($map2)->find();
            $v['e_money']='-';//账户消耗
            $v['e_zhuanhua']='-';//账户转化量
            $v['e_clue_num']='-';//导入进线量
            $v['e_chengben']='-';//成本计算
            $v['e_true_consume']='-';//Api进线消耗（真实消耗）
            $v['jinxiansunhao_rate']='-';//进线损耗率
            $v['avg_api_clue']='-';//真实成本

            if($v['clue_num']>0){
                //分发损耗率
                $v['push_rate']=bcsub(1,bcdiv($v['push_num'],$v['clue_num'],4),4)*100;
                $v['all_push_rate']='-';//总损耗率
                //重复
                $v['re_clue_num']=Db::name('clue_err_log')->alias('a')->where(['type'=>1,'city'=>$v['city'],'pro_id'=>$v['pro_id']])->where($map1)->count();
//                dump($v['re_clue_num'].'-'.$v['clue_num']);
                $v['re_clue_rate']=bcdiv($v['re_clue_num'],$v['clue_num'],4)*100;
//                dump($v['re_clue_rate']);
//                dump('----');
                //撞库
                $v['re_phone']=Db::name('clue_err_log')->alias('a')
                    ->join('clue b','a.clue_id=b.id AND b.status=0')
                    ->where(['a.type'=>2,'a.city'=>$v['city'],'a.pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_phone_rate']=bcdiv($v['re_phone'],$v['clue_num'],4)*100;
                //非目标城市
                $v['re_clue_city']=Db::name('clue_err_log')->alias('a')
                    ->join('clue b','a.clue_id=b.id AND b.status=0')
                    ->where(['a.type'=>3,'a.city'=>$v['city'],'a.pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_city_rate']=bcdiv($v['re_clue_city'],$v['clue_num'],4)*100;
            }else{
                $v['push_rate']='-';
                $v['all_push_rate']='-';//总损耗率
                //重复
                $v['re_clue_num']=Db::name('clue_err_log')->alias('a')->where(['type'=>1,'city'=>$v['city'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_clue_rate']='-';
                //撞库
                $v['re_phone']=Db::name('clue_err_log')->alias('a')->where(['type'=>2,'city'=>$v['city'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_phone_rate']='-';
                //非目标城市
                $v['re_clue_city']=Db::name('clue_err_log')->alias('a')->where(['type'=>3,'city'=>$v['city'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_city_rate']='-';
            }

            //分发客单
            if($v['push_num']>0){
                $v['push_avg_money']=bcdiv($v['price'],$v['push_num'],2);
            }else{
                $v['push_avg_money']=0;
            }

            $v['e_push_avg_money']='-';

            //收发总差额(分发总金额-(账户消耗*(1-返点)))，
//            $temp1=bcmul($v['price'],bcsub(1,$v['clue_rate']/100,3),2);
//            $v['sf_all_cha']=bcsub($v['price'],$temp1,2);
            //收发客单差额
            $v['e_sf_avg_sub']='-';

            if(isset($xiaohaoMoney['money'])&&$xiaohaoMoney['money']!==NULL){
                $v['e_money']=round($xiaohaoMoney['money'],2);
                $v['e_zhuanhua']=$xiaohaoMoney['transform_val'];
                $v['e_clue_num']=$xiaohaoMoney['clue_num'];
                $v['e_true_consume']=$xiaohaoMoney['true_consume'];
//                $v['e_chengben']=0;
//                $v['jinxiansunhao_rate']=1*100;//进线损耗率
//                $v['all_push_rate']=0;//总损耗率
                if($xiaohaoMoney['transform_val']>0){
                    $v['e_chengben']=bcdiv($xiaohaoMoney['money'],$xiaohaoMoney['transform_val'],2);
                    $v['jinxiansunhao_rate']=bcsub(1,bcdiv($v['clue_num'],$xiaohaoMoney['transform_val'],4),4)*100;
                    $v['all_push_rate']=bcsub(1,bcdiv($v['push_num'],$xiaohaoMoney['transform_val'],4),4)*100;//总损耗率
                }
                if($v['clue_num']>0){
                    //真实成本
                    $v['avg_api_clue']=bcdiv($xiaohaoMoney['true_consume'],$v['clue_num'],2);
                }else{
                    $v['avg_api_clue']=0;
                }
                //分发客单
//                if($v['push_num']>0){
//                    $v['e_push_avg_money']=bcdiv($xiaohaoMoney['money'],$v['push_num'],2);
//                }else{
//                    $v['e_push_avg_money']=0;
//                }
                //收发总差额(分发总金额-(账户消耗*(1-返点)))，
//                $temp1=bcmul($xiaohaoMoney['money'],bcsub(1,$v['clue_rate']/100,3),2);
                $v['e_sf_all_cha']=round(bcsub($v['price'],$v['e_true_consume'],3),2);
                //收发客单差额
                $v['e_sf_avg_sub']=round(bcsub($v['push_avg_money'],$v['avg_api_clue'],3),2);

            }else{
                $v['e_sf_all_cha']=$v['price'];
            }

            $nowXiaoHao+=floatval($v['e_money']); //导入的消耗
            $nowZhuanghua+=intval($v['e_zhuanhua']); //导入的转换量
            $apiJinXianXiaohao+=floatval($v['e_true_consume']);//导入计算的真实消耗
            $apiJixXianNum+=$v['clue_num'];
            $nowFenfaNum+=$v['push_num'];
            $reClueNum+=$v['re_clue_num'];
            $rePhone+=$v['re_phone'];
            $reCity+=$v['re_clue_city'];
            $pushMoney+=$v['price'];
            $nowShouFaAllCha+=floatval($v['e_sf_all_cha']);

        }
//        die;

//        dump($infos);

        if($nowZhuanghua>0){
            $nowChengBen=bcdiv($nowXiaoHao,$nowZhuanghua,2); //成本=消耗/转化
            $jinXianShunHaoRate=bcsub(1,bcdiv($apiJixXianNum,$nowZhuanghua,4),4)*100;
            $allShunHaoRare=bcsub(1,bcdiv($nowFenfaNum,$nowZhuanghua,4),4)*100;
        }else{
            $jinXianShunHaoRate='-';
            $allShunHaoRare='-';
            $nowChengBen='-';
        }
        if($apiJixXianNum>0){
            $apiJinXianChengBen=bcdiv($nowXiaoHao,$apiJixXianNum,2);
            $fenFaShunHaoRare=bcsub(1,bcdiv($nowFenfaNum,$apiJixXianNum,4),4)*100;
            $reClueRate=bcdiv($reClueNum,$apiJixXianNum,4)*100;
            $rePhoneRate=bcdiv($rePhone,$apiJixXianNum,4)*100;
            $reCityRate=bcdiv($reCity,$apiJixXianNum,4)*100;
        }else{
            $apiJinXianChengBen='-';
            $fenFaShunHaoRare='-';
            $reClueRate='-';
            $rePhoneRate='-';
            $reCityRate='-';
        }

        if($nowFenfaNum>0){
            $pushAvgMoney=bcdiv($pushMoney,$nowFenfaNum,2);
        }else{
            $pushAvgMoney=0;
        }

        $nowKeDanCha=bcsub($pushAvgMoney,$nowChengBen,2);





        return $this->fetch('',[
            'groupType' => $groupType,
            'channels' => $channels,
            'products' => $products,
            'infos' => $infos,
            'times' => $times,
            'acc_name' => $acc_name,
            'cityname' => $cityname,
            'adv_id' => $adv_id,
            'pro_id' => $pro_id,
            'channel_id' => $channel_id,
            'nowXiaoHao' => $nowXiaoHao,
            'nowZhuanghua' => $nowZhuanghua,
            'apiJinXianXiaohao' => $apiJinXianXiaohao,
            'apiJixXianNum' => $apiJixXianNum,
            'nowFenfaNum' => $nowFenfaNum,
            'reClueNum' => $reClueNum,
            'reClueRate' => $reClueRate,
            'rePhone' => $rePhone,
            'rePhoneRate' => $rePhoneRate,
            'reCity' => $reCity,
            'reCityRate' => $reCityRate,
            'pushMoney' => $pushMoney,
            'nowShouFaAllCha' => $nowShouFaAllCha,
            'nowChengBen' => $nowChengBen,
            'jinXianShunHaoRate' => $jinXianShunHaoRate,
            'allShunHaoRare' => $allShunHaoRare,
            'apiJinXianChengBen' => $apiJinXianChengBen,
            'fenFaShunHaoRare' => $fenFaShunHaoRare,
            'pushAvgMoney' => $pushAvgMoney,
            'nowKeDanCha' => $nowKeDanCha,
            'showTime' => $showTime,
        ]);
    }

    //城市投放导出
    public function accountClueTotalExprot(){
        $times = input('times','');
        $acc_name = input('acc_name','');
        $adv_id = input('adv_id','');
        $pro_id = input('pro_id','');
        $channel_id = input('channel_id',0);
        $groupType = input('gtype',2);//1账户维度2城市维度3商户维度
        $cityname = input('cityname','');

        $map = [];
        $map1 = [];
        $map3 = [];
        $map4 = [];


        //模糊查询城市
        if(!empty($cityname)){
            $cityIdArr=Db::name('city')->field('id,type,pid')->where('name','like',$cityname."%")->where('type',2)->find();
            if($cityIdArr){
                $map['a.city']=$cityIdArr['pid'].'-'.$cityIdArr['id'];
                $map4['a.city']=$cityIdArr['pid'].'-'.$cityIdArr['id'];
            }else{
                $map['a.city']=0;
                $map4['a.city']=0;
            }
        }


        if($pro_id!=''){
            $map['a.pro_id']=$pro_id;
            $map4['a.pro_id']=$pro_id;
        }

        if($times != ''){
            $time_arr = explode(' - ',$times);
            $showTime=$times;
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
            $map2['time'] = ['between',[$time_arr[0],$time_arr[1]]];
        }else{
            $times=date('Y-m-d').' 00:00:00 - '.date('Y-m-d').' 23:59:59';
            $showTime=date('Y-m-d').' - '.date('Y-m-d');
            $time_arr = explode(' - ',$times);
            $map['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1])]];
            $map2['time'] = ['between',[$time_arr[0],$time_arr[1]]];
        }

        $orderDy='a.id desc';

        $channels=$this->getChannelArray();
        $productArr = Db::name('product')->column('name','id');

        $group='a.city,a.pro_id';
        $infos=Db::name('clue')->alias('a')->field('a.city,c.clue_rate,count(a.id) clue_num,sum(b.price) price,a.adv_id,a.pro_id,a.type,c.acc_name,c.acc_desc,show_channel,c.id account_id,SUM(IF(a.status = 1,1,0)) push_num')
            ->join('clue_log b','b.clue_id=a.id','LEFT')
            ->join('account c','c.adv_id=a.adv_id','LEFT')
            ->where($map)->where('a.delete_time',null)->group($group)->order($orderDy)->select();

        $clue1=[];
        foreach ($infos as $v){
            $clue1[]=[
                'city'=>$v['city'],
                'pro_id'=>$v['pro_id']
            ];
        }
        $clue2=Db::name('clue_cost_log')->alias('a')->field('city,pro_id')->where($map2)->where($map4)->group($group)->select();
        $arr3 = array_filter($clue2, function($v) use ($clue1) { return ! in_array($v, $clue1);});

        foreach ($arr3 as $v){
            $infos[]=[
                'city'=>$v['city'],
                'clue_rate'=>0,
                'clue_num'=>0,
                'price'=>0,
                'pro_id'=>$v['pro_id'],
                'type'=>0,
                'acc_name'=>'',
                'acc_desc'=>'',
                'show_channel'=>'',
                'account_id'=>0,
                'push_num'=>0,
            ];
        }

        if(empty($infos)){
            echo '没有数据导出';
        }


//        $nowXiaoHao=0; //导入的消耗
//        $nowZhuanghua=0; //导入的转换量
//        $nowChengBen=0; //成本=消耗/转化
//        $apiJinXianXiaohao=0;//导入计算的真实消耗
//        $apiJixXianNum=0;
//        $jinXianShunHaoRate=0;//1-（API进线量/转化量）
//        $apiJinXianChengBen=0;//消耗/API进线量
//        $nowFenfaNum=0;
//        $fenFaShunHaoRare=0;//1-(分发数量/进线量);
//        $allShunHaoRare=0;//1-(推送/转化);
//        $reClueNum=0;
//        $rePhone=0;
//        $reCity=0;
//        $pushMoney=0;
//        $pushAvgMoney=0;//分发金额/分发数量
//        $nowShouFaAllCha=0;
//        $nowKeDanCha=0;//分发均价-真实成本;

        foreach ($infos as &$v){
            $v['shop_name']='未知';
            $v['shop_desc']='未知';
            $v['shop_username']='未知';
            $v['shop_phone']='未知';

            $v['pro_name']=$productArr[$v['pro_id']];
            //账户消耗
            $v['city_name']='未知';
            $v['channel_name']='';
            $v['acc']='';
            if($groupType==1){
                $v['channel_name']=$channels[$v['type']];
                $map3=['account_id'=>$v['account_id'],'pro_id'=>$v['pro_id']];
            }elseif($groupType==2){
                $map3=['city'=>$v['city'],'pro_id'=>$v['pro_id']];
                if($v['city'] != ''){
                    $ids = explode('-',$v['city']);
                    $cityNum=count($ids);
                    if($cityNum==1){
                        $v['city_name'] = $this->getCityName($ids[0]);
                    }elseif ($cityNum==2){
                        $prov = $this->getCityName($ids[0]);
                        $city = $this->getCityName($ids[1]);
                        if($prov == $city){
                            $v['city_name'] = $prov;
                        }else{
                            $v['city_name'] = $prov.'-'.$city;
                        }
                    }
                }
            }elseif($groupType==3){
                $v['shop_name']=$v['mer_name'];
                $v['shop_desc']=$v['mer_desc'];
                $v['shop_username']=$v['username'];
                $v['shop_phone']=$v['mer_phone'];
            }
            $xiaohaoMoney=Db::name('clue_cost_log')->field('count(id) log_num,sum(true_consume) true_consume,sum(money) money,sum(transform_val) transform_val,sum(clue_num) clue_num')->where($map3)->where($map2)->find();
            $v['e_money']='-';//账户消耗
            $v['e_zhuanhua']='-';//账户转化量
            $v['e_clue_num']='-';//导入进线量
            $v['e_chengben']='-';//成本计算
            $v['e_true_consume']='-';//Api进线消耗（真实消耗）
            $v['jinxiansunhao_rate']='-';//进线损耗率
            $v['avg_api_clue']='-';//真实成本

            $v['log_num']=empty($xiaohaoMoney['log_num'])?0:$xiaohaoMoney['log_num'];

            if($v['clue_num']>0){
                //分发损耗率
                $v['push_rate']=bcsub(1,bcdiv($v['push_num'],$v['clue_num'],4),4)*100;
                $v['all_push_rate']='-';//总损耗率
                //重复
                $v['re_clue_num']=Db::name('clue_err_log')->alias('a')->where(['type'=>1,'city'=>$v['city'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_clue_rate']=bcdiv($v['re_clue_num'],$v['clue_num'],4)*100;
                //撞库
                $v['re_phone']=Db::name('clue_err_log')->alias('a')
                    ->join('clue b','a.clue_id=b.id AND b.status=0')
                    ->where(['a.type'=>2,'a.city'=>$v['city'],'a.pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_phone_rate']=bcdiv($v['re_phone'],$v['clue_num'],4)*100;
                //非目标城市
                $v['re_clue_city']=Db::name('clue_err_log')->alias('a')
                    ->join('clue b','a.clue_id=b.id AND b.status=0')
                    ->where(['a.type'=>3,'a.city'=>$v['city'],'a.pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_city_rate']=bcdiv($v['re_clue_city'],$v['clue_num'],4)*100;
            }else{
                $v['push_rate']='-';
                $v['all_push_rate']='-';//总损耗率
                //重复
                $v['re_clue_num']=Db::name('clue_err_log')->alias('a')->where(['type'=>1,'city'=>$v['city'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_clue_rate']='-';
                //撞库
                $v['re_phone']=Db::name('clue_err_log')->alias('a')->where(['type'=>2,'city'=>$v['city'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_phone_rate']='-';
                //非目标城市
                $v['re_clue_city']=Db::name('clue_err_log')->alias('a')->where(['type'=>3,'city'=>$v['city'],'pro_id'=>$v['pro_id']])->where($map1)->count();
                $v['re_city_rate']='-';
            }

            //分发客单
            if($v['push_num']>0){
                $v['push_avg_money']=bcdiv($v['price'],$v['push_num'],2);
            }else{
                $v['push_avg_money']=0;
            }

            $v['e_push_avg_money']='-';

            //收发总差额(分发总金额-(账户消耗*(1-返点)))，
            $v['e_sf_all_cha']='-';
//            $temp1=bcmul($v['price'],bcsub(1,$v['clue_rate']/100,3),2);
//            $v['sf_all_cha']=bcsub($v['price'],$temp1,2);
            //收发客单差额
            $v['e_sf_avg_sub']='-';

            if(isset($xiaohaoMoney['money'])&&$xiaohaoMoney['money']!==NULL){
                $v['e_money']=round($xiaohaoMoney['money'],2);
                $v['e_zhuanhua']=$xiaohaoMoney['transform_val'];
                $v['e_clue_num']=$xiaohaoMoney['clue_num'];
                $v['e_true_consume']=$xiaohaoMoney['true_consume'];
//                $v['e_chengben']=0;
//                $v['jinxiansunhao_rate']=1*100;//进线损耗率
//                $v['all_push_rate']=0;//总损耗率
                if($xiaohaoMoney['transform_val']>0){
                    $v['e_chengben']=bcdiv($xiaohaoMoney['money'],$xiaohaoMoney['transform_val'],2);
                    $v['jinxiansunhao_rate']=bcsub(1,bcdiv($v['clue_num'],$xiaohaoMoney['transform_val'],4),4)*100;
                    $v['all_push_rate']=bcsub(1,bcdiv($v['push_num'],$xiaohaoMoney['transform_val'],4),4)*100;//总损耗率
                }
                if($v['clue_num']>0){
                    //真实成本
                    $v['avg_api_clue']=bcdiv($xiaohaoMoney['true_consume'],$v['clue_num'],2);
                }else{
                    $v['avg_api_clue']=0;
                }
                //分发客单
//                if($v['push_num']>0){
//                    $v['e_push_avg_money']=bcdiv($xiaohaoMoney['money'],$v['push_num'],2);
//                }else{
//                    $v['e_push_avg_money']=0;
//                }
                //收发总差额(分发总金额-(账户消耗*(1-返点)))，
//                $temp1=bcmul($xiaohaoMoney['money'],bcsub(1,$v['clue_rate']/100,3),2);
                $v['e_sf_all_cha']=round(bcsub($v['price'],$v['e_true_consume'],3),2);
                //收发客单差额
                $v['e_sf_avg_sub']=round(bcsub($v['push_avg_money'],$v['avg_api_clue'],3),2);

            }else{
                $v['e_sf_all_cha']=$v['price'];
            }

//            $nowXiaoHao+=floatval($v['e_money']); //导入的消耗
//            $nowZhuanghua+=intval($v['e_zhuanhua']); //导入的转换量
//            $apiJinXianXiaohao+=floatval($v['e_true_consume']);//导入计算的真实消耗
//            $apiJixXianNum+=$v['clue_num'];
//            $nowFenfaNum+=$v['push_num'];
//            $reClueNum+=$v['re_clue_num'];
//            $rePhone+=$v['re_phone'];
//            $reCity+=$v['re_clue_city'];
//            $pushMoney+=$v['price'];
//            $nowShouFaAllCha+=floatval($v['e_sf_all_cha']);

        }

        $newExcel = new Spreadsheet();
//获取当前操作sheet的对象
        $objSheet = $newExcel->getActiveSheet();
        $date = date('YmdHis',time());
        $name = '城市投放统计';
        $objSheet->setTitle($name);//设置当前sheet的标题
//设置表头

        $objSheet->setCellValue('A1', '城市')
            ->setCellValue('B1', '产品')
            ->setCellValue('C1', '账户消耗')
            ->setCellValue('D1', '账户转化量')
            ->setCellValue('E1', '账户成本')
            ->setCellValue('F1', 'API进线消耗')
            ->setCellValue('G1', 'API进线量')
            ->setCellValue('H1', '进线损耗率')
            ->setCellValue('I1', '真实成本')
            ->setCellValue('J1', '分发成功量')
            ->setCellValue('K1', '分发损耗率')
            ->setCellValue('L1', '总损耗率')
            ->setCellValue('M1', '重复量')
            ->setCellValue('N1', '重复率')
            ->setCellValue('O1', '非目标城市')
            ->setCellValue('P1', '非目标城市率')
            ->setCellValue('Q1', '撞库量')
            ->setCellValue('R1', '撞库率')
            ->setCellValue('S1', '分发总金额')
            ->setCellValue('T1', '分发客单')
            ->setCellValue('U1', '收发总差额')
            ->setCellValue('V1', '收发客单差额')
            ->setCellValue('W1', '导入记录数量')
        ;

        $objSheet->getColumnDimension('A')->setWidth(20);
        $objSheet->getColumnDimension('B')->setWidth(10);
        $objSheet->getColumnDimension('C')->setWidth(12);
        $objSheet->getColumnDimension('D')->setWidth(12);
        $objSheet->getColumnDimension('E')->setWidth(12);
        $objSheet->getColumnDimension('F')->setWidth(12);
        $objSheet->getColumnDimension('G')->setWidth(12);
        $objSheet->getColumnDimension('H')->setWidth(12);
        $objSheet->getColumnDimension('I')->setWidth(12);
        $objSheet->getColumnDimension('J')->setWidth(12);
        $objSheet->getColumnDimension('K')->setWidth(12);
        $objSheet->getColumnDimension('L')->setWidth(12);
        $objSheet->getColumnDimension('M')->setWidth(12);
        $objSheet->getColumnDimension('N')->setWidth(12);
        $objSheet->getColumnDimension('O')->setWidth(12);
        $objSheet->getColumnDimension('P')->setWidth(12);
        $objSheet->getColumnDimension('Q')->setWidth(12);
        $objSheet->getColumnDimension('R')->setWidth(12);
        $objSheet->getColumnDimension('S')->setWidth(12);
        $objSheet->getColumnDimension('T')->setWidth(12);
        $objSheet->getColumnDimension('U')->setWidth(12);
        $objSheet->getColumnDimension('V')->setWidth(12);
        $objSheet->getColumnDimension('W')->setWidth(12);

        $info = $infos;
        $dataCount = count($info);
        $k = 1;
        if($dataCount == 0){
            exit;
        }else {
            for ($i = 0; $i < $dataCount; $i++) {
                $k = $k + 1;
                $objSheet->setCellValue('A' . $k, $info[$i]['city_name'])
                    ->setCellValue('B' . $k, $info[$i]['pro_name'])
                    ->setCellValue('C' . $k, $info[$i]['e_money'])
                    ->setCellValue('D' . $k, $info[$i]['e_zhuanhua'])
                    ->setCellValue('E' . $k, $info[$i]['e_chengben'])
                    ->setCellValue('F' . $k, $info[$i]['e_true_consume'])
                    ->setCellValue('G' . $k, $info[$i]['clue_num'])
                    ->setCellValue('H' . $k, $info[$i]['jinxiansunhao_rate'].'%')
                    ->setCellValue('I' . $k, $info[$i]['avg_api_clue'])
                    ->setCellValue('J' . $k, $info[$i]['push_num'])
                    ->setCellValue('K' . $k, $info[$i]['push_rate'].'%')
                    ->setCellValue('L' . $k, $info[$i]['all_push_rate'].'%')
                    ->setCellValue('M' . $k, $info[$i]['re_clue_num'])
                    ->setCellValue('N' . $k, $info[$i]['re_clue_rate'].'%')
                    ->setCellValue('O' . $k, $info[$i]['re_clue_city'])
                    ->setCellValue('P' . $k, $info[$i]['re_city_rate'].'%')
                    ->setCellValue('Q' . $k, $info[$i]['re_phone'])
                    ->setCellValue('R' . $k, $info[$i]['re_phone_rate'].'%')
                    ->setCellValue('S' . $k, $info[$i]['price'])
                    ->setCellValue('T' . $k, $info[$i]['push_avg_money'])
                    ->setCellValue('U' . $k, $info[$i]['e_sf_all_cha'])
                    ->setCellValue('V' . $k, $info[$i]['e_sf_avg_sub'])
                    ->setCellValue('W' . $k, $info[$i]['log_num'])
                ;
            }
        }

        $filename = $name.$date;
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename='.$filename.'.xlsx');
        header('Cache-Control: max-age=0');
//
        $write = IOFactory::createWriter($newExcel, 'Xlsx');
        $write->save('php://output');

//        dump($infos);

//        if($nowZhuanghua>0){
//            $nowChengBen=bcdiv($nowXiaoHao,$nowZhuanghua,2); //成本=消耗/转化
//            $jinXianShunHaoRate=bcsub(1,bcdiv($apiJixXianNum,$nowZhuanghua,4),4)*100;
//            $allShunHaoRare=bcsub(1,bcdiv($nowFenfaNum,$nowZhuanghua,4),4)*100;
//        }else{
//            $jinXianShunHaoRate='-';
//            $allShunHaoRare='-';
//            $nowChengBen='-';
//        }
//        if($apiJixXianNum>0){
//            $apiJinXianChengBen=bcdiv($nowXiaoHao,$apiJixXianNum,2);
//            $fenFaShunHaoRare=bcsub(1,bcdiv($nowFenfaNum,$apiJixXianNum,4),4)*100;
//            $reClueRate=bcdiv($reClueNum,$apiJixXianNum,4)*100;
//            $rePhoneRate=bcdiv($rePhone,$apiJixXianNum,4)*100;
//            $reCityRate=bcdiv($reCity,$apiJixXianNum,4)*100;
//        }else{
//            $apiJinXianChengBen='-';
//            $fenFaShunHaoRare='-';
//            $reClueRate='-';
//            $rePhoneRate='-';
//            $reCityRate='-';
//        }
//
//        if($nowFenfaNum>0){
//            $pushAvgMoney=bcdiv($pushMoney,$nowFenfaNum,2);
//        }else{
//            $pushAvgMoney=0;
//        }
//
//        $nowKeDanCha=bcsub($pushAvgMoney,$nowChengBen,2);

    }

}